求解SQL语句
这是数据表，表名：test1
namescoredtime
A82012/1/16
B102012/1/15
C12012/1/15
A22012/1/14
A32012/1/13
B62012/1/11
C92012/1/12
要求是统计每个人最后一次得分情况。一条sql语句。
我的答案是：SELECT x.name,x.score,x.dtime
from test1 as x,test1 as y
where x.name=y.name and day(x.dtime)>day(y.dtime);
但是不正确，请高手帮忙。
[解决办法]
select * from test1 a where not exists(select 1 from test1 where name=a.name and a.dtime<dtime)
[解决办法]
select name,score,dtime
from (
select name,score,dtime,ROW_NUMBER()OVER(PARTITION BY name ORDER BY dtime desc) AS RN from test1
) where rn=1
[解决办法]
SELECT x.name,x.score,x.dtime from test1 as x
where exists(select 1 from test1 where x.name=name
group by name having max(dtime)=x.dtime)
[解决办法]
USE test
GO
-->生成表test1
if object_id('test1') is not null
drop table test1
Go
Create table test1([name] nvarchar(1),[score] smallint,[dtime] datetime)
Insert into test1
Select N'A',8,'2012/1/16'
Union all Select N'B',10,'2012/1/15'
Union all Select N'C',1,'2012/1/15'
Union all Select N'A',2,'2012/1/14'
Union all Select N'A',3,'2012/1/13'
Union all Select N'B',6,'2012/1/11'
Union all Select N'C',9,'2012/1/12'
select * from test1
SELECT * FROM test1 AS a
WHERE NOT EXISTS(SELECT 1 FROM test1 AS x
WHERE x.name=a.name
AND x.dtime>a.dtime
)
/*
name score dtime
---- ------ -----------------------
A 8 2012-01-16 00:00:00.000
B 10 2012-01-15 00:00:00.000
C 1 2012-01-15 00:00:00.000
*/
