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 * from t a left join (select id,max(month)as month from t group by id )b on a.id=b.id and a.month=b.month
這樣也可以select * from t a where not exists(select 1 from t where id=a.id and month<a.month)
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 ,3select * 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 */
上面数据特殊,改一下: 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', 2 ,8 union all select '001', 3 ,20 union all select '001', 3 ,50 union all select '002', 2 ,5 union all select '003', 4 ,9 union all select '003', 5 ,6 union all select '003', 5 ,3select * from @a a where not exists(select 1 from @a where (id=a.id and month>a.month) or (id=a.id and month=a.month and code>a.code)) --result /*id month code ---------- ----------- ----------- 001 3 50 002 2 5 003 5 6 */
select * from tablename where month=(select max(month) from tablename 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 '002 ', 2 ,5 union all select '003 ', 4 ,9 union all select '003 ', 5 ,6 union all select '003 ', 5 ,3 select * from @a t where not exists(select 1 from @a where id=t.id and month>t.month)
declare @t table (id char(3) not null,month int,code int) insert @t select '001',1,2 union all select '001',1,2 union all select '001',3,2 union all select '001',1,2 union all select '002',2,2 union all select '003',4,2 union all select '003',5,2 select * from @t as a where month in (select max(month) from @t group by id)
declare @t table(id nvarchar(20),month int,code int) insert into @t select '001',1,2 insert into @t select '001',2,2 insert into @t select '001',3,8 insert into @t select '002',2,5 insert into @t select '003',4,9 insert into @t select '003',5,6 /* select * from @t */ declare @tt table(id nvarchar(20),month1 int)insert into @tt select id,max(month) as month1 from @t group by idselect id,month,code from @t inner join @tt on @[email protected]
declare @a table(id varchar(10),month int,code int) insert into @a select '001',1,2 union all select '001',2,2 union all select '001',3,8 union all select '002',2,5 union all select '003',4,9 union all select '003',5,6 select id,month,code from @a a where code=(select max(code) from @a where id=a.id) order by id asc
SELECT ID,MAX(MONTH),MAX(CODE) FROM TBL GROUP BY ID
select id ,max(month),code from tbl 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 '002 ', 2 ,5 union all select '003 ', 4 ,9 union all select '003 ', 5 ,6 union all select '003 ', 5 ,3 select id,month,max(code)as code from @a a where not exists(select * from @a b where a.id=b.id and a.month<b.month ) group by id,month order by id
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 * from t a
left join
(select id,max(month)as month from t group by id )b
on a.id=b.id and a.month=b.month
這樣也可以select * from t a
where not exists(select 1 from t where id=a.id and month<a.month)
where month=(select Top 1 month from tb T where id=tb.id order by month desc)
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
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 ,3select * 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
*/
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', 2 ,8
union all select '001', 3 ,20
union all select '001', 3 ,50
union all select '002', 2 ,5
union all select '003', 4 ,9
union all select '003', 5 ,6
union all select '003', 5 ,3select * from @a a where not exists(select 1 from @a where (id=a.id and month>a.month) or (id=a.id and month=a.month and code>a.code))
--result
/*id month code
---------- ----------- -----------
001 3 50
002 2 5
003 5 6
*/
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 '002 ', 2 ,5
union all select '003 ', 4 ,9
union all select '003 ', 5 ,6
union all select '003 ', 5 ,3 select *
from @a t
where not exists(select 1 from @a where id=t.id and month>t.month)
insert @t select '001',1,2 union all
select '001',1,2 union all
select '001',3,2 union all
select '001',1,2 union all
select '002',2,2 union all
select '003',4,2 union all
select '003',5,2
select * from @t as a where month in (select max(month) from @t group by id)
insert into @t select '001',1,2
insert into @t select '001',2,2
insert into @t select '001',3,8
insert into @t select '002',2,5
insert into @t select '003',4,9
insert into @t select '003',5,6
/*
select *
from @t
*/
declare @tt table(id nvarchar(20),month1 int)insert into @tt
select id,max(month) as month1
from @t
group by idselect id,month,code
from @t inner join @tt on @[email protected]
insert into @a
select '001',1,2 union all
select '001',2,2 union all
select '001',3,8 union all
select '002',2,5 union all
select '003',4,9 union all
select '003',5,6
select id,month,code from @a a
where code=(select max(code) from @a where id=a.id) order by id asc
SELECT ID,MAX(MONTH),MAX(CODE) FROM TBL
GROUP BY ID
group by id
insert @a select '001 ', 1 , 2
union all select '001 ', 2 ,2
union all select '001 ', 3 ,8
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 id,month,max(code)as code from @a a where not exists(select * from @a b where a.id=b.id and a.month<b.month )
group by id,month order by id
那用7楼的方法就可以了