分组取每组几条数据

mysql8.0之前sql实现

rownumber() over(partition by xxx order by xxx )语句，即：对表分组后排序

创建测试emp表

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：

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

另外一种写法

SELECT S1.*,(SELECT COUNT(1) FROM TEST.`test_course` S2 WHERE S1.CLASS = S2.CLASS AND S2.GRADE > S1.GRADE)+1
AS "paiming"
FROM TEST.`test_course` S1 HAVING paiming <=3 ORDER BY S1.CLASS,paiming;

解析SQL：

3>...

目的是获取前三条数据，可以多次修改

emp where deptno = a.deptno

目的是确定分组字段

sal < a.sal

`排序条件

结果图：

原文转载 https://www.cnblogs.com/duyunchao-2261/p/7460294.html

mysql8.0之后使用窗口函数实现

今天接到一个需求，要求按照某几个字段分组，每个分组提取 10 条数据，百度了一下，大部分文章介绍的都是一下这种方法：

select * from emp as a where 10> (select count(*) from emp where deptno = a.deptno and sal < a.sal  )  order by a.deptno ,a.sal

以上方法我没有测试出来，但是在继续搜索中得知，mysql8 之后可以使用 PARTITION BY 语法，所以换用以下方式成功提取到数据：

SELECT a.* FROM (SELECT *, row_number() over (PARTITION BY deptno, sal) AS num FROM emp ) a WHERE a.num <= 10

参考:

mysql8 分组后随机取 n 条数据 - 膨胀的面包
https://blog.wangtwothree.com/code/145.html

mysql 每个分类取5条_MySQL：如何查询出每个分组中的 top n 条记录？_weixin_39730284的博客-CSDN博客
https://blog.csdn.net/weixin_39730284/article/details/110587189