oracle或mysql获取分组后每组的前三条数据

目录

• 1 分组排序查询
  • 1.1 引言
  • 1.2 子查询
    • 1.2.1 方法一
      • 1.2.1.1 方法分析
    • 1.2.2 方法二
    • 1.2.3 方法三
  • 1.3 自定义变量
    • 1.3.1 SQL分析
  • 1.4 窗口函数
    • 1.4.1 mysql
    • 1.4.2 oracle

1 分组排序查询

1.1 引言

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

• 连续排名：例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号
• 同薪同名但总排名不连续：例如同样的薪水分布，排名结果为1-2-2-4
• 同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3

值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数

1.2 子查询

1.2.1 方法一

排名第N的记录 意味着该表中存在N-1个比其更高的记录
注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个
最后返回的记录也应该去重，因为可能不止一个记录排名第N
由于对于每个记录的where条件都要执行一遍子查询，注定其效率低下

select a.* 
from
(
select t1.*,(select count(*)+1 from 表 where 分组字段=t1.分组字段 and 排序字段<t1.排序字段) as group_id
from 表 t1
) a
where a.group_id<=3

1.2.1.1 方法分析

在查询列中使用嵌套子查询，在嵌套子查询中用要分组的字段作为关联字段，采用内关联，分组字段不是唯一的，查出就会翻倍，再用排序字段错位比较大小，就可以得出条数。但是 把条数查询不是放在列中而是放在where后就只会有一个条数，而不会每行都有条数，如下：(假定只查询部门编号是20)

把求行数子查询放在where后面
select * from employee e1 ,employee e2 where e1.deptno=e2.deptno 
 and e1.sal<e2.sal and e1.deptno = '20'

再对其求行数处理：(假定只查询部门编号是20)

把求行数子查询放在where后面
select count(*) from employee e1 ,employee e2 where e1.deptno=e2.deptno 
 and e1.sal<e2.sal and e1.deptno = '20'

要想能查出前三行，并且每行都显示行数，就必须这样处理：(假定只查询部门编号是20)

select t1.*,(select count(*)+1 from employee where deptno=t1.deptno and sal<t1.sal) as group_id
from employee t1 where t1.deptno = '20'

缺点：如果一个部门下存在同样的工资金额，那么就会有两个一样的行号了

1.2.2 方法二

和方法一差不多，但是没有把排序的行放在记录中

SELECT
    *
FROM
    employee tpn
WHERE
    (
        SELECT
            COUNT(*)
        FROM
            employee t
        WHERE
            tpn.deptno = t.deptno AND tpn.sal <= t.sal
    ) < 3

1.2.3 方法三

此处的建表语句是模拟按照每位学生分组后，成绩排名前三的要去

CREATE TABLE `score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stu_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subject_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入语句

INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (1, '1', '语文', 86);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (2, '1', '英语', 88);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (3, '1', '数学', 93);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (4, '1', '体育', 80);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (5, '2', '语文', 83);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (6, '2', '数学', 87);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (7, '2', '英语', 79);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (8, '2', '体育', 90);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (9, '3', '语文', 86);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (10, '3', '数学', 83);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (11, '3', '英语', 81);
INSERT INTO `score`(`id`, `stu_id`, `subject_name`, `score`) VALUES (12, '3', '体育', 86);

此处是SQL还是用了子查询语句的分数来进行排序，同时要注意在子查询语句中使用limit语法的话，需要在嵌套一层，不然会提示语法错误

select * from 
score a
where score in (
	select score from ( 
		select score from score s where s.stu_id = a.stu_id order by  score desc limit 3
	) a 
)

1.3 自定义变量

自定义变量可以解决同部门下相同工资却有同样排序编号问题

SET @rank:=0;
SELECT * FROM 
(SELECT a.*,
	IF(@tmp=deptno,@rank:=@rank + 1,@rank:=1) AS group_id,
	@tmp:=deptno AS tmp
	
	FROM employee a  ORDER BY deptno,sal DESC) b
WHERE b.group_id<=5

如图：

1.3.1 SQL分析

采用了局部变量方法，mysql变量详情理解：https://jingzh.blog.csdn.net/article/details/96328410
倘若只有一条信息，可能会让临时变量一直增加所以先这样设置SET @rank:=0;
核心语句：
IF(@tmp=deptno,@rank:=@rank + 1,@rank:=1)则利用中间变量@tmp存储上一条记录的deptno，并和当前的对比，如若相同，则序号@rank增加1，否则初始化@rank为1
@tmp:=deptno AS tmp则用于将当前的province_name值记录下来，供下一条记录使用

1.4 窗口函数

1.4.1 mysql

在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：

• row_number(): 同记录不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
• rank(): 同记录同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
• dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
• ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

• partition by，按某字段切分
• order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据

SELECT t.*         
   FROM (
   	SELECT ROW_NUMBER() 
   		OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC) rn,         
         b.*         
    FROM 表 b) t         
  WHERE t.rn <= 3  ;

1.4.2 oracle

oracle中是使用over函数

SELECT t.*         
   FROM (SELECT ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC) rn,         
         b.*         
         FROM 表 b) t         
  WHERE t.rn <= 3  ;

此处使用了析构函数
Oracle中的分析函数over()的详细解析
两种写法都是获取分组中的伪列序号为目的