【sql】分组取每组第一行值
576 ✦ 843842144@qq.com
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

结果显示1,3,4效率相同,2,5效率差些  

--需要把限制查询条件写在子查询中 否则可能查询条数变少,因为取top的时候没有限制可能取到的rpstatus不是29
--错误
select a.col_r_no,a.col_rp_status, a.* from sys_cos_report_tracklog a where
a.col_rp_status='29' and a.id = (select top(1) b.id from sys_cos_report_tracklog b where b.col_r_no=a.col_r_no)
--正确
select a.col_r_no,a.col_rp_status, a.* from sys_cos_report_tracklog a where
a.id = (select top(1) b.id from sys_cos_report_tracklog b where b.col_r_no=a.col_r_no and b.col_rp_status='29')


Submit
回复
11111
回复
22222
回复
33333
回复
44444