mysql分组求最值（模拟Oracle的分析函数）

小鱼儿 2022-07-18 01:55 112阅读 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;

编辑于17.10.12：

这两天需要用到这方面的知识，但这个sql连我自己看着都麻烦，从网上找到被别人点过赞的。我也改写放到线上了。后来线上的一个数据还是有问题。分组取最值（或者说是需要的几条数据）失败。我这个麻烦归麻烦，写完也用下试试吧，结果真没问题。线上N个sql都用的这个求最值。但sql直接看实在有点乱。我再整理下吧：

上面的sql总结下就是下面的这些，已经颜色区分开了

SELECT aa, bb, cc,  activityID,rank FROM (

SELECT aa, bb, cc,activityID, IF(@orderField=maxb.activityID, @rank:=@rank+1, @rank:=1) AS rank,@orderField:=maxb.activityIDFROM (

SELECT aa, bb, cc,activityIDFROM dates ORDER BY activityID  DESC,endDate DESC, endTime DESC

)maxb, (SELECT @orderField:=NULL, @rank:=0) maxc

) result HAVING rank <= 1;

上面需要注意的就是：

例如一个活动有可能有N个时间地点信息，现在要找出M个活动中最新的时间地点信息，按理说就是M条数据，参考：[http://bjmx.xdf.cn/huodong/][http_bjmx.xdf.cn_huodong]的应用示例（要把已结束的活动标记出来）

1.排序，我想把数据按照活动id分组，求出每个活动最新的时间地点信息（dates这个表，按照活动id、日期倒序、时间倒序排序）

自己的业务如要使用，第一个排序就是要分组的字段；第二个排序字段之后，就是求最值的字段，比如求时间最大值就按照时间倒序，时间最小值就是按照时间正序，这个我不用多说就好理解，这里面用黄色背景标出

2.分组，要对哪个进行分组，红色字体就替换成那个字段即可，这里面是activityID

3.可能要查其它字段，直接在绿色背景上标出即可，这里面是aa, bb, cc

[Center]: /images/20220717/b7c2b618098547b989e68c3d04fd2141.png
[Center 1]: /images/20220717/010dc796c9a64b378d947602e4742a32.png
[http_bjmx.xdf.cn_huodong]: http://bjmx.xdf.cn/huodong/