在实际的业务场景中遇到了数据提取的问题: 只保留每个id某两列值最大的一行,忽略业务提炼要求: 表test按照列字段grade,grade2的数值大小进行从高到低排序, 针对每一个主键id取出排序后的第一行数据,即找出每个id的grade,grade2均较高的一行数据;下面介绍两种实现方式:
使用order by 再进行group by
分析:group by更多的是和聚合函数一起使用,达到分组统计的效果,或者和having搭配使用对分组后的结果进行条件筛选,这里使用group by不是进行计数,而是利用了抽取第一行数据的特性。对排序后的结果进行group by 恰恰抽取到了想要grade,grade2两列值均最高的一行数据。
test示例表:
select * from test
在Mysql中使用子查询的方式,order by和group by 一起使用。执行以下语句:
select a.* from
(select * from test
order by grade desc, grade2 desc) a
group by a.id
发现:id为肖战,取出来的数据不对,应该是体育的一行。所以,order by 未起到作用,实际上只是group by 抽取了第一行数据;
查了下网上的解决办法,发现可以通过在order by 后加limit语句的方式来解决。
https://blog.csdn.net/m0_37830658/article/details/103583107
select a.* from
(select * from test
order by grade desc, grade2 desc limit 1000) a
group by a.id
尝试后发现此办法可行,运行结果正确。但是存在一个问题,当数据表量较大时,limit后的数字要超过数据总行数,执行效率大大降低。
使用row_number函数,对分组排序后的数据增加行索引
分析: 1. 按id进行分组,按grade,grade2进行行排序,每组增加行索引
SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY grade DESC,grade2 DESC) AS row_num
FROM test
2.取出以上每组行号为1的数据;
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY grade DESC,grade2 desc) AS row_num
FROM test
) AS ranked
WHERE row_num = 1;
结果正确,此方法可行。