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