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

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

如图:


image.png

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()的详细解析
两种写法都是获取分组中的伪列序号为目的

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 199,830评论 5 468
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 83,992评论 2 376
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 146,875评论 0 331
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 53,837评论 1 271
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 62,734评论 5 360
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,091评论 1 277
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,550评论 3 390
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,217评论 0 254
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,368评论 1 294
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,298评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,350评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,027评论 3 315
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,623评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,706评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,940评论 1 255
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,349评论 2 346
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 41,936评论 2 341

推荐阅读更多精彩内容