有一表结构,如下?
work_day,lbt1,cout1
21 1 500
1 1 33
50 1 45
22 2 400
2 2 46
需要得到如下记录
work_day,lbt1,cout1
21 1 500
22 2 400
请问如何处理?小弟太菜,请指点下?
work_day,lbt1,cout1
21 1 500
1 1 33
50 1 45
22 2 400
2 2 46
需要得到如下记录
work_day,lbt1,cout1
21 1 500
22 2 400
请问如何处理?小弟太菜,请指点下?
from a join (
select lbt1,max(cout1) as cout1 from tb
) b on a.lbt1=b.lbt1 and a.cout1 =b.cout1
(
work_day int,
lbt1 int,
cout1 int
)
insert into #EE select 21,1,500
union all select 1,1,33
union all select 50,1,45
union all select 22,2,400
union all select 2,2,46
select * from #EE t where not exists (select * from #EE where lbt1=t.lbt1 and cout1>t.cout1 )
work_day lbt1 cout1
----------- ----------- -----------
21 1 500
22 2 400(2 行受影响)
*
from
tb t
where
cout1 in(select top 1 cout1 from tb where lbt1=t.lbt1 order by lbt1 desc)
select top 2 * from tb order by cout1 desc
work_day,lbt1,cout1
21 1 500
1 1 33
50 1 45
22 2 400
2 2 46
需要得到如下记录
work_day,lbt1,cout1
21 1 500
22 2 400
请问如何处理?小弟太菜,请指点下?
这个问题没有说完整 根据lbt1取work_day最大值,
还是根据lbt1取cout1 最大值,
还是根据lbt1取cout1 最大值和work_day最大值。
create table #(work_day int,lbt1 int,cout1 int)
insert into # select 21,1,500
union all select 1,1,33
union all select 50,1,45
union all select 22,2,400
union all select 2,2,46
select * from # a where cout1 in(select max(cout1) from # where lbt1=a.lbt1)
/*
work_day lbt1 cout1
----------- ----------- -----------
21 1 500
22 2 400
*/根据lbt1的值分组先取得cout1的最大值,然后获取所在的行数据
select * from A t where not exists (select * from A where lbt1=t.lbt1 and cout1<t.cout1 )這種速度最快了.
select * from tb a,
(select lbt1, max(cout1) from tb group by lbt1)b
where a.lbt1=b.lbt1 and a.cout1 = b.cout1