sql-server - 如何查询 sql 以获取最新的记录日期值
问题描述
我有以下结构
username, date, value, IDexam, No.Points
--------------------------
brad, 1/2/2010, not pass exam1 10
brad, 2/2/2010, pass exam1 15
brad, 1/3/2010, pass exam2 20
brad, 1/3/2010, not pass exam3 12
fred, 1/3/2010, pass exam2 25
fred, 5/3/2010, pass exam2 20
fred, 5/4/2010, not pass exam3 12
etc..
我想要的是:
username, Exam1-value, Exam1-date, Exam1-points, Exam2-value, Exam2-date, Exam2-points, Exam3-value, Exam3-date, Exam3-points
--------------------------
brad, pass, 2/2/2010 15, pass, 1/3/2010, 20, , ,
fred, , , , pass, 5/3/2010 20, not pass, 5/4/2010, 12
因此,对于同一个 IDExam 的多条记录,以获得同一用户的最新记录,但在列上提前感谢
解决方案
尝试这个:
CREATE TABLE #DataSource
(
[username] VARCHAR(16)
,[date] DATE
,[value] VARCHAR(16)
,[IDexam] VARCHAR(16)
,[No.Points] TINYINT
);
INSERT INTO #DataSource ([username], [date], [value], [IDexam], [No.Points])
VALUES ('brad', '1/2/2010', 'not pass', 'exam1', 10)
,('brad', '2/2/2010', 'pass', 'exam1', 15)
,('brad', '1/3/2010', 'pass', 'exam2', 20)
,('brad', '1/3/2010', 'not pass', 'exam3', 12)
,('fred', '1/3/2010', 'pass', 'exam2', 25)
,('fred', '5/3/2010', 'pass', 'exam2', 20)
,('fred', '5/4/2010', 'not pass', 'exam3', 12);
SELECT [username]
,MAX(IIF([IDexam] = 'exam1', [date], NULL)) AS [Exam1-value]
,MAX(IIF([IDexam] = 'exam1', [value], NULL)) AS [Exam1-date]
,MAX(IIF([IDexam] = 'exam1', [No.Points], NULL)) AS [Exam1-points]
,MAX(IIF([IDexam] = 'exam2', [date], NULL)) AS [Exam2-value]
,MAX(IIF([IDexam] = 'exam2', [value], NULL)) AS [Exam2-date]
,MAX(IIF([IDexam] = 'exam2', [No.Points], NULL)) AS [Exam2-points]
,MAX(IIF([IDexam] = 'exam3', [date], NULL)) AS [Exam3-value]
,MAX(IIF([IDexam] = 'exam3', [value], NULL)) AS [Exam3-date]
,MAX(IIF([IDexam] = 'exam3', [No.Points], NULL)) AS [Exam3-points]
,MAX(IIF([IDexam] = 'exam4', [date], NULL)) AS [Exam4-value]
,MAX(IIF([IDexam] = 'exam4', [value], NULL)) AS [Exam4-date]
,MAX(IIF([IDexam] = 'exam4', [No.Points], NULL)) AS [Exam4-points]
,MAX(IIF([IDexam] = 'exam5', [date], NULL)) AS [Exam5-value]
,MAX(IIF([IDexam] = 'exam5', [value], NULL)) AS [Exam5-date]
,MAX(IIF([IDexam] = 'exam5', [No.Points], NULL)) AS [Exam5-points]
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY [username], [IDexam] ORDER BY [date] DESC) AS [RowID]
FROM #DataSource
) DS
WHERE [RowID] = 1
GROUP BY [username]
DROP TABLE #DataSource;
推荐阅读
- angular - Angular 6 等待 Observable
- ethereum - 当发送者在函数执行过程中耗尽气体时会发生什么?
- sql - 根据 SQL 中的日期范围创建设置日期的动态方法
- azure-active-directory - AADSTS65005:动态范围无效 - Files.Read
- jenkins - Jenkins Warning Plugin 4.68 Pylint 1.9 Python 2.7 Reports and Graphs are Basic/Dated - How to Configure for new style
- c# - 如何从 WMI 获取所有可用的 PNPClass 名称
- r - 根据条件查找组内的差异
- python - 将张量流 conv2d 转换为 numpy/scipy 操作?
- php - 如何在我的 oop php 项目中显示登录用户名?
- javascript - 使用 javascript 编写取决于环境的锚链接