与oracle的 rownumber() over(partition by xxx order by xxx )语句类似，即：对表分组后排序
创建测试emp表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno decimal(4,0) NOT NULL,
ename varchar(10) DEFAULT NULL,
job varchar(9) DEFAULT NULL,
mgr decimal(4,0) DEFAULT NULL,
hiredate datetime DEFAULT NULL,
sal decimal(7,2) DEFAULT NULL,
comm decimal(7,2) DEFAULT NULL,
deptno decimal(2,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ------------------------------ 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');
|
需求：按部门分组后显示工资为前三的员工信息
SQL：
|
1
|
select * from emp as a where 3> (select count(*) from emp where deptno = a.deptno and sal < a.sal ) order by a.deptno ,a.sal
|
解析SQL：
3>...
目的是获取前三条数据，可以多次修改
emp where deptno = a.deptno
目的是确定分组字段
sal < a.sal
排序条件
结果图：
zhuanzi https://www.cnblogs.com/duyunchao-2261/p/7460294.html