与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