表:TEST
NAME TIME VALUE
A 200608 23
B 200507 25
A 200511 28
C 200408 29
B 200607 20
C 200508 12得下结果:
Name TIME VALUE
A 200507 28
B 200507 25
C 200408 29
NAME TIME VALUE
A 200608 23
B 200507 25
A 200511 28
C 200408 29
B 200607 20
C 200508 12得下结果:
Name TIME VALUE
A 200507 28
B 200507 25
C 200408 29
insert test(name,time,value)
select 'A','200608','23' union all
select 'A','200608','23' union all--这条是增加的测试数据,如果日期相同,则都会显示出来
select 'B','200507','25' union all
select 'A','200511','28' union all
select 'C','200408','29' union all
select 'B','200607','20' union all
select 'C','200508','12'
--select * from test
select * from test a
WHERE 1>(SELECT COUNT(*) FROM test b WHERE a.time<b.time and a.name=b.name)
drop table test
inner join
(select name,max(time) mt from test group by name) b
on a.name=b.name and a.time=b.mt
NAME TIME VALUE
A 200608 23
B 200507 25
A 200511 28
C 200408 29
B 200607 20
C 200508 12得下结果:
Name TIME VALUE
A 200507 28 --这个地方应该是A 200511 28吧!
B 200507 25
C 200408 29
left join (select name,max(time) as max from test group by name) b
on a.name=b.name and a.time=b.max
select * from test a where time=(select top 1 time from test where name = a.name order by VALUE desc,abs(datediff(hour,time.getdate()))