如何在每两行后的行中插入减法并将其插入到新列中

如何在每两行后的行中插入减法并将其插入到新列中

假设下表结构

CREATE TABLE [15853354] -- Stack Overflow question number(    [user-name] VARCHAr(20),    [submissions] INT,    [date] DATE,    [score] NUMERIC(9,2),    [points] NUMERIC(9,1))INSERT [15853354]VALUES    ('abkqz', 5, '12 JUL 2010', 83.91, 112.5),    ('abkqz', 5, '9 JUN 2010', 77.27, 0),    ('abkqz', 5, '17 MAY 2010', 91.87, 315)

然后，您可以编写以下查询

;WITH [cte15853354] AS(    SELECt         [user-name],        [submissions],        [date],        [score],        [points],        ROW_NUMBER() OVER (ORDER BY [user-name], [date] DESC) AS [ROWNUMBER]    FROM [15853354])SELECt     t.[user-name],    t.[submissions],    DATEDIFF(DAY, ISNULL([t-1].[date],t.[date]),t.[date]) AS [recency],    t.[score],    t.[points]FROM [cte15853354] tLEFT JOIN [cte15853354] [t-1]    ON [t-1].[user-name] = t.[user-name]    AND [t-1].[ROWNUMBER] = t.[ROWNUMBER] + 1

这使用“公用表表达式”来计算行号，然后执行自联接将每一行与下一行联接，然后计算以天为单位的日期差。