日期:2014-05-18 浏览次数:20634 次
id month code 001 1 2 001 2 2 001 3 8 002 2 5 003 4 9 003 5 6 希望得到,每个id的最大月份的code是多少。 id month code 001 3 8 002 2 5 003 5 6
select id, code from tb where month=(select Top 1 month from tb T where id=tb.id order by month desc)
------解决方案--------------------
這樣也可以
select * from t a
where not exists(select 1 from t where id=a.id and month <a.month)
----------
如果对于一个ID,最大的month有重复,就会得到多条记录,,,
select id, code from tb
where month=(select Top 1 month from tb T where id=tb.id order by month desc)
--------
每个ID一条记录。
------解决方案--------------------
错了,好像也会查到多条记录,,,
得再改改:
select id, max(code) from tb
where month=(select Top 1 month from tb T where id=tb.id order by month desc)
group by id
------解决方案--------------------
declare @a table(id varchar(10), month int, code int)
insert @a select '001', 1 , 2
union all select '001', 2 ,2
union all select '001', 3 ,8
union all select '001', 3 ,20
union all select '002', 2 ,5
union all select '003', 4 ,9
union all select '003', 5 ,6
union all select '003', 5 ,3
select * from @a a where not exists(select 1 from @a where id=a.id and (month>a.month) or (month=a.month and code>a.code))
--result
/*
id month code
---------- ----------- -----------
001 3 20
002 2 5
003 5 6
*/