oracle多列最大值的sql

最新推荐文章于 2023-09-18 15:30:33 发布

原创最新推荐文章于 2023-09-18 15:30:33 发布 · 640 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql

本文探讨了如何使用SQL查询优化技术，通过行号和排序等方法，对数据进行高效聚合，实现对特定数据集的深入分析。重点介绍了在特定查询场景下，如何通过调整查询策略，提高查询性能和数据准确性。

select id,r,sr,kbn from (
select distinct id
,row_number() over(partition by id order by r desc) rn
,row_number() over(partition by id,r order by sr desc) rn2
,row_number() over(partition by id,r,sr order by kbn desc) rn3
,r
,sr
,kbn

from test_soukou
)
where rn = 1 and rn2=1 and rn3=1

--order by id,v1,v2,v3

select a.id, a.r, a.sr, max(kbn) as kbn

from
(
select t.id, t.r, max(sr) as sr
from (select id, max(r) as r from test_soukou group by id) t1, test_soukou t
where t1.id = t.id and t1.r = t.r group by t.id, t.r
) a, test_soukou b
where a.id = b.id and a.r=b.r and a.sr=b.sr group by a.id,a.r,a.sr