我有一张表,上面有学生信息。
+==========================================+ | ID | Department | Date | +==========================================+ | 001 | English | Feb 3 2017 | | 001 | English | Feb 4 2017 | | 001 | Science | Mar 1 2017 | | 001 | Maths | Mar 2 2017 | | 001 | Maths | Mar 21 2017 | | 001 | Maths | Apr 2 2017 | | 001 | English | Apr 7 2017 | | 002 | Maths | Feb 1 2017 | | 002 | Maths | Apr 7 2017 | | 003 | Maths | Apr 3 2017 | | 003 | Maths | Apr 7 2017 | | 004 | Science | Feb 1 2017 | | 004 | Science | Mar 1 2017 | | 004 | Maths | Apr 7 2017 | | 004 | English | Apr 9 2017 | +==========================================+
在上表中,无论何时更改学生的部门偏好,我都需要获取学生记录列表。学生还有机会再次改回同一部门。因此,对于上述示例数据,返回的记录列表为
对于学生001
| 001 | English | Feb 4 2017 | | 001 | Science | Mar 1 2017 | | 001 | Maths | Apr 2 2017 |
002和003什么都没有
为004
| 004 | Science | Mar 1 2017 | | 004 | Maths | Apr 7 2017 |
您可以使用LEAD窗口功能-适用于SQL版本2012及更高版本…
LEAD
DECLARE @SampleData AS TABLE ( Id int, Department varchar(20), [Date] date ) INSERT INTO @SampleData VALUES (1,'English', 'Feb 3 2017'),(1,'English', 'Feb 4 2017'),(1,'Science', 'Mar 1 2017'), (1,'Maths', 'Mar 2 2017'),(1,'Maths', 'Mar 3 2017'),(1,'English', 'Mar 7 2017'), (2,'Maths', 'Feb 3 2017'),(2,'Maths', 'Feb 4 2017'), (3,'Maths', 'Feb 3 2017'), (3,'Maths', 'Feb 4 2017'), (4,'Science', 'Feb 1 2017'), (4,'Science', 'Feb 2 2017'), (4,'Maths', 'Feb 3 2017'),(4,'English', 'Feb 4 2017') ;WITH temps AS ( SELECT sd.*, LEAD(sd.Department, 1) OVER(PARTITION BY id ORDER BY sd.[Date]) AS NextDepartment FROM @SampleData sd ) SELECT t.id, t.Department,t.[Date] FROM temps t WHERE t.Department != t.NextDepartment
演示链接:Rextester
参考链接:LEAD-MDSN
对于较旧的版本,您可以使用 OUTER APPLY
OUTER APPLY
SELECT sd.* FROM @SampleData sd OUTER APPLY ( SELECT TOP 1 * FROM @SampleData sd2 WHERE sd.Id = sd2.Id AND sd.[Date] < sd2.[Date] ) nextDepartment WHERE sd.Department != nextDepartment.Department