用这条语句
select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc
查询得到下图结果现在我要得到下图结果。用company_id 去重复,但是条件要是quantity值在同一company_id下最大的值
高手们帮帮忙呀
select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc
查询得到下图结果现在我要得到下图结果。用company_id 去重复,但是条件要是quantity值在同一company_id下最大的值
高手们帮帮忙呀
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
and not exists(select 1 from info_fair_job where company_id = t.company_id and
quantity>t.quantity order by quantity desc)
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)
and company_id = (select max(company_id) from info_fair_job where quantity = t.quantity)
order by quantity desc
and quantity = (select max(quantity) from info_fair_job where company_id= t.company_id)
order by quantity desc
--修改一下
因为quantity 在不现的company_id下面会有相同的情况,可以会少取到些数据
这条也可以,但是好像我有点看不懂 with ces as 是什么意思,呵呵。
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
--擦少加了个条件
五楼的取的数据不太准确。
http://hi.csdn.net/attachment/201109/17/10136356_13162247767qmg.jpg只能取到两条,quantity等于6的两条中只有一条了
但是我不是取job_id最大的,因为很多时修,job_id最大,但是quantity 不一定是最大的
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 desc2.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)3.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
(
select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc
) m where quantity = (select max(quantity) from
(
select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc
) n where m.company_id = n.company_id
)--orselect m.* from
(
select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc
) m where not exists(select 1 from
(
select top 8 * from info_fair_job where is_stop=0 and batch_id=49 order by quantity desc
) n where m.company_id = n.company_id and n.quantity > m.quantity
)