如何从下面数据id aa_id money amonth ayear
1 1 32 1 2005
2 1 34 2 2005
3 2 234 1 2005
4 2 5645 2 2005
5 2 34 3 2005
6 1 324 2 2006
7 1 43534 6 2006得到:
id aa_id money amonth ayear
2 1 34 2 2005
5 2 34 3 2005
7 1 43534 6 2006 也就是说,如何得到相同aa_id,相同ayear,amonth最大的数据.
谢谢!
1 1 32 1 2005
2 1 34 2 2005
3 2 234 1 2005
4 2 5645 2 2005
5 2 34 3 2005
6 1 324 2 2006
7 1 43534 6 2006得到:
id aa_id money amonth ayear
2 1 34 2 2005
5 2 34 3 2005
7 1 43534 6 2006 也就是说,如何得到相同aa_id,相同ayear,amonth最大的数据.
谢谢!
select max(amonth) as maxmonth,aa_id, ayear from view1 group by aa_id,ayear
where not exists
( select 1 from table1 where A.aa_id=aa_id and A.ayear=ayear and amonth<A.amonth)
declare @t table (id int,aa_id int ,money int,amonth int,ayear int)
insert @t select 1,1,32,1,2005
union all select 2,1,34,2,2005
union all select 3,2,234,1,2005
union all select 4,2,5645,2,2005
union all select 5,2,34,3,2005
union all select 6,1,324,2,2006
union all select 7,1,43534,6,2006
select * from @t A
where not exists
( select 1 from @t where A.aa_id=aa_id and A.ayear=ayear and amonth>A.amonth)
declare @t table (id int,aa_id int ,money int,amonth int,ayear int)
insert @t select 1,1,32,1,2005
union all select 2,1,34,2,2005
union all select 3,2,234,1,2005
union all select 4,2,5645,2,2005
union all select 5,2,34,3,2005
union all select 6,1,324,2,2006
union all select 7,1,43534,6,2006
select * from @t A
where id in (select top 1 id from @t where aa_id=A.aa_id and ayear=A.ayear order by amonth desc)
select * from tab a
where not exists(select 1 from tab b where a.aa_id = b.aa_id and a.id < b.id )or select a.* from tab a ,
(
select max(id) as id, aa_id
from tab group by aa_id
) b
where a.id = b.id and a.aa_id = b.aaid