有表A,列有 TIME时间,NAME 姓名,achievement成绩,如表中有如下数据
,
我想按名字分类取出如下数据
,都是按时间找的,这个SQL请问该怎么写
有表A,列有 TIME时间,NAME 姓名,achievement成绩,如表中有如下数据
,
我想按名字分类取出如下数据
,都是按时间找的,这个SQL请问该怎么写
根据你个情况 模拟了一个,望采纳
CREATE TABLE [dbo].[A](
[TIME] [date] NOT NULL,
[NAME] [nvarchar](100) NOT NULL,
[SCORE] [int] NOT NULL
) ON [PRIMARY]
WITH S AS
(SELECT
B.NAME,
B.SCORE as 最后一次成绩,
B.TIME as 最后一次时间
FROM
(SELECT *,ROW_NUMBER()OVER(PARTITION BY [NAME] ORDER BY [TIME] DESC) AS RN
FROM A)B
where B.RN=1),
d as
(SELECT
B.NAME,
B.SCORE as 上一次成绩,
B.TIME as 上一次时间
FROM
(SELECT *,ROW_NUMBER()OVER(PARTITION BY [NAME] ORDER BY [TIME] DESC) AS RN
FROM A)B
where B.RN=2)
select s.NAME,s.最后一次成绩,s.最后一次时间,d.上一次成绩,d.上一次时间 from S join d
on s.NAME=d.name
ORDER BY s.最后一次时间