您可以使用
LEAD窗口功能-适用于SQL版本2012及更高版本…
DECLARE @SampleData AS TABLE ( Id int, Department varchar(20), [Date] date)INSERT INTO @SampleDataVALUES (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 tWHERe t.Department != t.NextDepartment
演示链接：Rextester
参考链接：LEAD-MDSN
对于较旧的版本，您可以使用
OUTER APPLY
SELECt sd.*FROM @SampleData sdOUTER APPLY ( SELECt TOP 1 * FROM @SampleData sd2 WHERe sd.Id = sd2.Id AND sd.[Date] < sd2.[Date]) nextDepartmentWHERe sd.Department != nextDepartment.Department
