SQL按特殊字段排序去重复

如下图数据用

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里面性能稍好一点。

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) 

转载于:https://www.cnblogs.com/huailian/archive/2011/09/17/2179488.html