我有一张桌子:
ID | Name | Date
1 | ABC | 2015-01-01
2 | XYZ | 2015-01-02
3 | ABC | 2015-01-03
4 | ABC | 2015-01-04
我想查询该表,使结果变为:
ID | Name | Date | NextDate
1 | ABC | 2015-01-01 | 2015-01-03
2 | XYZ | 2015-01-02 | null
3 | ABC | 2015-01-03 | 2015-01-04
4 | ABC | 2015-01-04 | null
一种解决方案是:
select t1.*,
(select min(t2.Date) from TAB t2 where t2.ID > t1.ID t2.Name = t1.Name) NextDate
from TAB t1
但这非常慢,因为我们在每一行上进行聚合。是否有其他解决方案比上面更有效?
由于SQL SERVER 2008
不支持LEAD
窗口功能,我们必须使用row_number
和对其进行仿真self join
。试试这个
;WITH cte
AS (SELECT t1.*,
Row_number()
OVER(
partition BY Name
ORDER BY [Date]) AS rn
FROM TAB t1)
SELECT a.ID,a.Name,a.Date,b.Date as nextdate
FROM cte a
LEFT OUTER JOIN cte b
ON a.Name = b.Name
AND a.rn + 1 = b.rn ORDER BY a.ID
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句