create table test([NAME] char(1),[TIME] char(6),VALUE int)
insert test(name,time,value)
select 'A','200606','28' union all
select 'A','200608','28' union all--,如果value相同,则只显示最后日期的一个,其它不显示
select 'B','200507','25' union all
select 'A','200501','27' union all
select 'C','200408','92' union all
select 'B','200607','20' union all
select 'B','200608','25' union all
select 'd','200608','25' union all
select 'C','200508','30'
--select * from test
select * from test a
WHERE 1>(SELECT COUNT(*) FROM test b WHERE a.value<b.value and a.name=b.name)drop table testNAME TIME VALUE
---- ------ -----------
A 200606 28
A 200608 28
B 200507 25
C 200408 92
B 200608 25
d 200608 25
(所影响的行数为 6 行)我想要的结果为:
NAME TIME VALUE
---- ------ -----------
A 200608 28
C 200408 92
B 200608 25
d 200608 25应该怎么改写?谢谢了
insert test(name,time,value)
select 'A','200606','28' union all
select 'A','200608','28' union all--,如果value相同,则只显示最后日期的一个,其它不显示
select 'B','200507','25' union all
select 'A','200501','27' union all
select 'C','200408','92' union all
select 'B','200607','20' union all
select 'B','200608','25' union all
select 'd','200608','25' union all
select 'C','200508','30'
GO
Select A.* From test A
Inner Join
(Select name,Max([time]) As [time] From test Group By name) B
On A.name=B.name And A.[time]=B.[time]
Order By A.name
GO
Drop Table test
Where Not Exists (Select name From test Where name=A.name And [time]>A.[time])
Order By A.nameSelect A.* From test A
Where [time]=(Select Max([time]) From test Where name=A.name)
Order By A.name
我把它改成:
Select A.* From test A
Inner Join
(Select name,Max([value]) As [value] From test Group By name) B
On A.name=B.name And A.[value]=B.[value]
Order By A.name
还是没有得到我要的结果
insert test(name,time,value)
select 'A','200606','28' union all
select 'A','200608','28' union all--,如果value相同,则只显示最后日期的一个,其它不显示
select 'B','200507','25' union all
select 'A','200501','27' union all
select 'C','200408','92' union all
select 'B','200607','20' union all
select 'B','200608','25' union all
select 'd','200608','25' union all
select 'C','200508','30'
Select a.name,a.value,max(a.time) From test A
Inner Join
(Select name,Max(value) As [value] From test Group By name) B
On A.name=B.name And A.[value]=B.[value]
group by a.name,a.value order by a.namedrop table test
Inner Join
(Select name,Max(value) As value From test Group By name) B
On A.name=B.name And A.value=B.value
Group By A.name,A.value
Order By A.name