mysql模拟Oracle(select t.*, row_number() over(partition by...)...实现分组排序功能

拼搏现实的明天。 2022-04-11 07:21 247阅读 0赞

我们知道无论是oracle，还是mysql，只要对某列分组，就只能查询分组列或者分组函数列，而对于分组后的整体数据单单靠一个分组函数查询不出来。

在以前开发时，使用的是oracle数据库，比如有很多年的数据，根据年限分组，获取每组最大值，在oracle中可以这样实现：

select *
      from (select t.*,
                   row_number() over(partition by t.dqdm order by t.nf desc) cn --这么理解，按dqdm分组，每组按nf降序，这样row_number() 这列（别名cn） 会按照分好的组，每组都1、2、3，1、2、3的排
              from t_sjk_dqmjxx t
             where t.scbj = '0')
    where cn = '1'--这时取第一个就是按dqdm排序，取每组年份最大的字段值了

但mysql没有类似的函数，需要我们使用用户变量来模拟实现类似的功能：

1.测试数据表结构：

CREATE TABLE `stud` (
      `id` varchar(30) NOT NULL,
      `name` varchar(30) NOT NULL,
      `score` int(11) DEFAULT NULL,
      `subject` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.插入测试数据

insert into `stud` (`id`, `name`, `score`, `subject`) values('1001','张三','98','语文');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1002','张三','86','数学');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1003','张三','73','英语');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1004','李四','85','语文');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1005','李四','92','数学');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1006','李四','79','英语');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1007','王五','68','语文');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1008','王五','79','数学');
    insert into `stud` (`id`, `name`, `score`, `subject`) values('1009','王五','92','英语');

整理完是这样的表结构：

![Center][]

3.使用sql查询分组

![Center 1][]

这里有几个说明的地方：

A:一定要自己手动排好序，因为我本意是按照科目分组，查出每个科目分数最高的学员信息，因此先按照subject排序，再按照score降序（asc查询的就是每科中成绩最低的学员信息了）

B:仿照这个改sql时，除了自己的表字段以外，B处是要手动替换的字段，因为我需要科目分组，所以需要写成subject，按照每个科目的rank进行1、2、3...这样的展示

C:rank <=1，1代表了选取每组第一行的数据

完成sql如下展示：

SELECT id, `name`, score, `subject`, rank FROM (
    	SELECT b.id, b.name, b.score, b.subject, IF(@pdept=b.subject, @rank:=@rank+1, @rank:=1) AS rank, @pdept:=b.subject FROM (
    		SELECT id, `name`, score, `subject` FROM stud ORDER BY `subject`, score DESC
    	) b, (SELECT @pdept:=NULL, @rank:=0) c 
    ) result HAVING rank <= 1;

[Center]: /images/20220411/f158b4d1ecb04c3a9de7e94481fe98cd.png
[Center 1]: /images/20220411/15ed4c76fa7744c1bb7a474a099fc8c4.png