62,041
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #(id int identity(1,1),name nvarchar(20),score int,adddate datetime)
INSERT # SELECT '张三',80,'2011-2-10 8:03:20'
UNION ALL SELECT '李四',10,'2011-2-10 8:01:15'
UNION ALL SELECT '王五',20,'2011-2-10 8:02:10'
UNION ALL SELECT '张三',50,'2011-2-10 8:10:20'
UNION ALL SELECT '张三',40,'2011-2-10 8:00:40'
UNION ALL SELECT '王五',60,'2011-2-10 8:04:24'
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY name ORDER BY adddate DESC) row FROM #
) T
WHERE row=1 ORDER BY adddate DESC
/*
--适用于2005及以上版本
id name score adddate row
----------- -------------------- ----------- ----------------------- --------------------
4 张三 50 2011-02-10 08:10:20.000 1
6 王五 60 2011-02-10 08:04:24.000 1
2 李四 10 2011-02-10 08:01:15.000 1
(3 行受影响)
*/
select max(百分比),事件 group by 事件 --类似于这个,可以自动扩展