sql

mysql 分組排序 取前top n

力扣mysql 題目為:

Employee 表包含所有員工資訊,每個員工有其對應的工號 Id,姓名 Name,工資 Salary 和部門編號 DepartmentId 。

+—-+——-+——–+————–+
| Id | Name  | Salary | DepartmentId |
+—-+——-+——–+————–+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+—-+——-+——–+————–+

Department 表包含公司所有部門的資訊。

+—-+———-+
| Id | Name     |
+—-+———-+
| 1  | IT       |
| 2  | Sales    |
+—-+———-+

編寫一個  查詢,找出每個部門獲得前三高工資的所有員工。例如,根據上述給定的表,查詢結果應返回:

+————+———-+——–+
| Department | Employee | Salary |
+————+———-+——–+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+————+———-+——–+

解釋:

IT 部門中,Max 獲得了最高的工資,Randy 和 Joe 都拿到了第二高的工資,Will 的工資排第三。銷售部門(Sales)只有兩名員工,Henry 的工資最高,Sam 的工資排第二。

來源:力扣(LeetCode)
連結:https://leetcode-cn.com/problems/department-top-three-salaries

解題思路

  1 使用開窗函式

  1. SELECT
  2. Department.NAME AS 'Department',
  3. a.NAME AS 'Employee',
  4. a.Salary
  5. FROM
  6. ( SELECT NAME, Salary, DepartmentId, DENSE_RANK() over ( PARTITION BY departmentid ORDER BY salary DESC ) AS rrank FROM Employee ) a
  7. JOIN Department
  8. WHERE
  9. Department.id = a.DepartmentId
  10. AND a.rrank <= 3

這裡使用 DENSE_RANK 因為這裡的分組排序取得是成績前三,而不是排名前三的人, 像IT 部門結果有四人

使用開窗函式的前提 版本需要大於8才支援

2 原生mysql 語句

  1. SELECT
  2. Department.NAME AS Department,
  3. e1.NAME AS Employee,
  4. e1.Salary AS Salary
  5. FROM
  6. Employee AS e1,Department
  7. WHERE
  8. e1.DepartmentId = Department.Id
  9. AND 3 > (SELECT count( DISTINCT e2.Salary )
  10. FROM Employee AS e2
  11. WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId )
  12. ORDER BY Department.NAME,Salary DESC;

有的小夥伴可能看不明白上面的sql ,彆著急

這裡需要解釋一下原生sql如何實現分組求top n ,上述題目的簡化版, 引用一下別人的案例,可以放到資料庫自己測試:

  1. DROP TABLE IF EXISTS `emp`;
  2. CREATE TABLE `emp` (
  3. `empno` decimal(4, 0) NOT NULL,
  4. `ename` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  5. `job` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  6. `mgr` decimal(4, 0) NULL DEFAULT NULL,
  7. `hiredate` datetime(0) NULL DEFAULT NULL,
  8. `sal` decimal(7, 2) NULL DEFAULT NULL,
  9. `comm` decimal(7, 2) NULL DEFAULT NULL,
  10. `deptno` decimal(2, 0) NULL DEFAULT NULL
  11. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  12. -- ----------------------------
  13. -- Records of emp
  14. -- ----------------------------
  15. INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20);
  16. INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30);
  17. INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30);
  18. INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20);
  19. INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30);
  20. INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30);
  21. INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10);
  22. INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20);
  23. INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10);
  24. INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30);
  25. INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20);
  26. INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30);
  27. INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20);
  28. INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10);
  29. SET FOREIGN_KEY_CHECKS = 1;

匯入資料得到一下表資料

每個部門對應top3 sql

  1. SELECT
  2. *
  3. FROM
  4. emp e
  5. WHERE
  6. ( SELECT count( 1 ) FROM emp WHERE deptno = e.deptno AND e.sal < sal ) < 3
  7. ORDER BY
  8. deptno,
  9. sal

2.1  解釋當前count的執行過程

拿deptno 為30舉例  sal 的值對應[1600.00,1250.00,1250.00,2850.00,1500.00,950.00] ,e.sal依次遍歷 sal 也會依次遍歷

當e.sal=1600.00時, e.sal<sal 存在 [2850.00]    1條記錄    

當e.sal=1250.00時, e.sal<sal 存在 [1600.00,2850.00,1500.00]   3條記錄

當e.sal=2850.00時, e.sal<sal 存在0 條記錄

同理依次遍歷, e.sal對應  e.sal<sal 存在n條, e.sal 在當前的分組排名則為n+1; count 的含義就是代表比當前sal大的記錄條數, (比如成績排名,比第二名靠前的 只有1個人,比第三名靠前的,只有2個人)

所有判斷count 條數小於3,對應排名則為 前3。

 

回顧原題目, 它需要查詢的是Salary 工資排名前三的人,相同Salary 的同樣的排名    所以查詢count 多了DISTINCT,

這樣同樣分數的人,排名就會一致,符合題目要求。 

 

 

 

 

 

本文章已修改原文用詞符合繁體字使用者習慣使其容易閱讀

版權宣告:此處為CSDN博主「記錄每一份筆記」的原創文章,依據CC 4.0 BY-SA版權協議,轉載請附上原文出處連結及本宣告。

原文連結:https://blog.csdn.net/yumingzhu1/article/details/108254255