表格如下: id1 id2 num
01 1 3
01 2 4
01 3 1
01 4 5
01 5 11
01 6 10
01 7 8
01 8 8
.. .. .. 02 1 13
02 2 41
02 3 51
02 4 51
02 5 111
02 6 101
02 7 81
02 8 189
.. .. ..
希望得到如下结果:
id1 id2 num
01 5 11
02 8 189并且确定上面两个值是唯一的,而且最大。
01 1 3
01 2 4
01 3 1
01 4 5
01 5 11
01 6 10
01 7 8
01 8 8
.. .. .. 02 1 13
02 2 41
02 3 51
02 4 51
02 5 111
02 6 101
02 7 81
02 8 189
.. .. ..
希望得到如下结果:
id1 id2 num
01 5 11
02 8 189并且确定上面两个值是唯一的,而且最大。
where not exists(select 1 from tb where id1=a.id1 and num>a.num)
select id1,id2=(select id2 from tb where id1=a.id1 and num=max(a.num)),num=max(num)
from tb a
group by id1
Set Nocount On
declare @1 table([id1] nvarchar(2),[id2] int,[num] int)
Insert @1
select N'01',1,3 union all
select N'01',2,4 union all
select N'01',3,1 union all
select N'01',4,5 union all
select N'01',5,11 union all
select N'01',6,10 union all
select N'01',7,8 union all
select N'01',8,8 union all
select N'02',1,13 union all
select N'02',2,41 union all
select N'02',3,51 union all
select N'02',4,51 union all
select N'02',5,111 union all
select N'02',6,101 union all
select N'02',7,81 union all
select N'02',8,189
Select * From @1 a Where num=(Select Max(num) From @1 Where id1=a.id1) Order By id1
/*
id1 id2 num
---- ----------- -----------
01 5 11
02 8 189
*/
select a.* from tb a
join (select id1,num=max(num) from tb group by id1) b
on a.id1=b.id1 and a.num=b.num
from
(select id1,max(num) as num
from table
group by id) a,table b
where a.id1=b.id1 and a.num=b.num
select * from tb a where not exists(select * from tb b where b.num>a.num)
上面少了 a.id=b.id
insert into @t select 01,1,3
union all select 01,2,4
union all select 01,3,1
union all select 01,4,5
union all select 01,5,11
union all select 01,6,10
union all select 01,7,8
union all select 01,8,8
union all select 02,1,13
union all select 02,2,41
union all select 02,3,51
union all select 02,4,51
union all select 02,5,111
union all select 02,6,101
union all select 02,7,81
union all select 02,8,189
----------
--select * from @t
--select * from @t a where not exists
--(select 1 from @t where id1=a.id1 and id2>a.id2 and id1>a.id1)
--select id1,id2=(select id2 from @t where id1=a.id1 and num=max(a.num)),
-- num=max(num) from @T a
--group by id1
select a.* from @t a ,(select id1,max(num)num from @t group by id1)b
where a.id1=b.id1 and a.num=b.num
order by a.id11 5 11
2 8 189
where id1 =01
union
select id1,id2,max(num) from B
where id =02