MySQL Group takes the first n data of each group

Similar to Oracle's RowNumber () over (partition by xxx order by xxx ) statement, which is: sorting tables after grouping

Creating a Test EMP table

DROP TABLE IF EXISTS emp; CREATE TABLE emp (empno decimal (4,0) NOT NULL, ename varchar (TEN) default NULL, Job varchar (9) default NULL, Mgr Decim Al (4,0) default NULL, HireDate datetime default NULL, sal decimal (7,2) default NULL, Comm Decimal (7,2) default NULL, D Eptno 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 INSERT into EMP VALUES (' 7499 ', ' ALLEN ', ' salesman ', ' 7698 ', ' 1981-02-20 00:00:00 ', ' 1600.00 ', ' 300.00 ', ' + '); INS  ERT into EMP VALUES (' 7521 ', ' WARD ', ' salesman ', ' 7698 ', ' 1981-02-22 00:00:00 ', ' 1250.00 ', ' 500.00 ', ' + '); INSERT into EMP  VALUES (' 7566 ', ' JONES ', ' MANAGER ', ' 7839 ', ' 1981-04-02 00:00:00 ', ' 2975.00 ', null, ') ', INSERT into emp VALUES (' 7654 ',  ' MARTIN ', ' salesman ', ' 7698 ', ' 1981-09-28 00:00:00 ', ' 1250.00 ', ' 1400.00 ', ' + '); INSERT into emp VALUES (' 7698 ', ' BLAKE ',' manager ', ' 7839 ', ' 1981-05-01 00:00:00 ', ' 2850.00 ', null, ' + '); INSERT into emp VALUES (' 7782 ', ' CLARK ', ' manager ', ' 7839 ', ' 1981-06-09 00:00:00 ', ' 2450.00 ', NULL, ' ten '); INSERT into emp VALUES (' 7788 ', ' SCOTT ', ' ANALYST ', ' 7566 ', ' 1982-12-09 0 0:00:00 ', ' 3000.00 ', null, ' + '); INSERT into emp VALUES (' 7839 ', ' KING ', ' president ', NULL, ' 1981-11-17 00:00:00 ', ' 5000.0 0 ', NULL, ' ten '); INSERT into emp VALUES (' 7844 ', ' TURNER ', ' salesman ', ' 7698 ', ' 1981-09-08 00:00:00 ', ' 1500.00 ', ' 0.00 ', ' 3 0 '); INSERT into EMP VALUES (' 7876 ', ' ADAMS ', ' clerk ', ' 7788 ', ' 1983-01-12 00:00:00 ', ' 1100.00 ', null, ' + '); INSERT INTO EM P VALUES (' 7900 ', ' JAMES ', ' clerk ', ' 7698 ', ' 1981-12-03 00:00:00 ', ' 950.00 ', null, ' + '); INSERT into emp VALUES (' 7902 ', '  FORD ', ' ANALYST ', ' 7566 ', ' 1981-12-03 00:00:00 ', ' 3000.00 ', ' null ', ' ' + '); INSERT into emp VALUES (' 7934 ', ' MILLER ', ' clerk ', ' 7782 ', ' 1982-01-23 00:00:00 ', ' 1300.00 ', null, ' 10 ');

Requirement: Show employee information for the top three of payroll after grouping by department

Sql:

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

　　

Parsing sql:

3>, .....

The goal is to get the first three data, which can be modified multiple times

EMP WHERE deptno = A.deptno

To determine the grouping field

Sal < A.sal

Sorting criteria

Result diagram:

　　

MySQL Group takes the first n data of each group