sql server 去重复 转帖的

select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc

 

按company_id 去重复，取同一company_id下quantity最大值

 以下三种方按，其中第一种主法在sql2005里面性能稍好一点。

View Code?

1.select top 8 * from info_fair_job t where is_stop=0 and batch_id=49 

and quantity = (select max(quantity) from info_fair_job where company_id= t.company_id and is_stop=0 and batch_id=49) 

order by quantity desc 

  

2.select top 8 * 

from info_fair_job t 

where not exists (select 1 from info_fair_job where company_id = t.company_id and quantity > t.quantity and is_stop=0 and batch_id=49) 

  and is_stop=0 and batch_id=49 

order by quantity desc 

  

3.with cet as( 

select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc ) 

select * from cet t where not exists(select 1 from cet where t.company_Id=company_id and t.quantity<quantity)