表TB中，在gid為1和2中，要把souceid和type同時(shí)相同的找出來，只取一條
id souceid type gid
1?? s1 ????? t1?????? 1
2?? s1 ????? t1?????? 2
3?? s2 ????? t2?????? 1
4?? s2 ????? t2?????? 2
5?? s3?????? t3?????? 1
我了解的三種方法分別利用in、join、row_number
1.select * from TB where id in (select Max(id) from TB where gid in(1,2) group by souceid,type)
2.select * from TB A join (select Max(id) id from TB where gid in(1,2) group by souceid,type) B on A.id=B.id
3.select * from (select id,souceid,type,gid,row_number() over (partition by souceid,type order by id) as rn) A where A.rn=1
請(qǐng)高手指正，哪種方法最高效亦或是有更好的改進(jìn)方法
Sql去重性能比較
哆啦的時(shí)光機(jī)
2018-12-06 12:08:04
