根据需要查询出来的结果一共三列,分别是kao-卡号、bianh-店铺id、res-订单号,实际结果是卡号在不同的店铺会有不同的订单号,而这里想要的结果是对于相同卡号在不同店铺的消费只显示任何一条记录即可,使得对应的每个卡号只有一条记录。实际结果:
KAO bianh res
001 A01 rs234
002 C01 rs235
003 C01 rs236
002 C01 rs237
003 B01 rs238
004 B01 rs239想要结果:(相同卡号可随意保留一条记录)
KAO bianh res
001 A01 rs234
002 C01 rs235
003 C01 rs236
004 B01 rs239
KAO bianh res
001 A01 rs234
002 C01 rs235
003 C01 rs236
002 C01 rs237
003 B01 rs238
004 B01 rs239想要结果:(相同卡号可随意保留一条记录)
KAO bianh res
001 A01 rs234
002 C01 rs235
003 C01 rs236
004 B01 rs239
select KAO,bianh,res
from (select KAO,bianh,res,rn=row_number() over(partition by KAO order by getdate())
from [表名]) t
where rn=1
select a.KAO,a.bianh,a.res
from [表名] a join (select min(res) res,KAO,bianh from [表名]) b on a.KAO=b.KAO and a.bianh=b.bianh
或者用
select * from T a where not exists(select * from T where KAO = a.KAO and res < a.res)