sql根据某列去除重复数据

需求：查询出订单中金额满899元的客户，赠送礼品，相同不能重复赠送

分析：有些客户可能会有多个订单超过899，而且相同的昵称也有可能有不同的收货人，地址等，所以不能单纯的用distinct

网上搜了下：大致思路如下，给查询的数据加一个自增列，然后碰到相同的昵称则取其中一个即可。

 

select distinct buyer_nick,receiver_name,ISNULL(receiver_state,'')+','+ISNULL(receiver_city,'')+','+ISNULL(receiver_district,'')+','+ISNULL(receiver_address,'')+',' addr,'' '1',receiver_mobile,'CL60035/1s*1' sku,'' post   code,'满899送60035/1s' memo, identity(int,1,1) rownumber into #t
from orders
where 
(Payment-isnull(post_fee,0))>=899 and pay_time between '2013-01-01' and '2013-09-14'
and seller_nick='**旗舰店'
and status not like '%closed%'
and is_main=1 

select * from #t where rownumber in(
    select MAX(rownumber) rownumber from #t a where a.buyer_nick=#t.buyer_nick group by a.buyer_nick11     )
drop table #t

最后一段也可以改成

select * from #t where rownumber in(
    select top 1 rownumber from #t a where a.buyer_nick=#t.buyer_nick
    )

两者在执行效率上也相差无几。