力扣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 |
+—-+———-+
編寫一個 SQL 查詢,找出每個部門獲得前三高工資的所有員工。例如,根據上述給定的表,查詢結果應返回:
+————+———-+——–+
| 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 使用開窗函式
- SELECT
- Department.NAME AS 'Department',
- a.NAME AS 'Employee',
- a.Salary
- FROM
- ( SELECT NAME, Salary, DepartmentId, DENSE_RANK() over ( PARTITION BY departmentid ORDER BY salary DESC ) AS rrank FROM Employee ) a
- JOIN Department
- WHERE
- Department.id = a.DepartmentId
- AND a.rrank <= 3
這裡使用 DENSE_RANK 因為這裡的分組排序取得是成績前三,而不是排名前三的人, 像IT 部門結果有四人
使用開窗函式的前提 mysql 版本需要大於8才支援
2 原生mysql 語句
- SELECT
- Department.NAME AS Department,
- e1.NAME AS Employee,
- e1.Salary AS Salary
- FROM
- Employee AS e1,Department
- WHERE
- e1.DepartmentId = Department.Id
- AND 3 > (SELECT count( DISTINCT e2.Salary )
- FROM Employee AS e2
- WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId )
- ORDER BY Department.NAME,Salary DESC;
有的小夥伴可能看不明白上面的sql ,彆著急
這裡需要解釋一下原生sql如何實現分組求top n ,上述題目的簡化版, 引用一下別人的案例,可以放到資料庫自己測試:
- DROP TABLE IF EXISTS `emp`;
- CREATE TABLE `emp` (
- `empno` decimal(4, 0) NOT NULL,
- `ename` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
- `job` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
- `mgr` decimal(4, 0) NULL DEFAULT NULL,
- `hiredate` datetime(0) NULL DEFAULT NULL,
- `sal` decimal(7, 2) NULL DEFAULT NULL,
- `comm` decimal(7, 2) NULL DEFAULT NULL,
- `deptno` decimal(2, 0) NULL DEFAULT NULL
- ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-
- -- ----------------------------
- -- Records of emp
- -- ----------------------------
- INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20);
- INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30);
- INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30);
- INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20);
- INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30);
- INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30);
- INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10);
- INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20);
- INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10);
- INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30);
- INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20);
- INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30);
- INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20);
- INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10);
-
- SET FOREIGN_KEY_CHECKS = 1;
匯入資料得到一下表資料
每個部門對應top3 sql
- SELECT
- *
- FROM
- emp e
- WHERE
- ( SELECT count( 1 ) FROM emp WHERE deptno = e.deptno AND e.sal < sal ) < 3
- ORDER BY
- deptno,
- 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