如何在SQL Server交易表中并列显示当前与上周ValueAmount

表结构 #

现有SQL Server表包含以下列：

[Username], [Team], [ID] (* primary key), [DateEntered], [Task], [T_ID], [ValueAmount]

其中[T_ID]为任务分组标识（例如任务a、b、c、d、e的T_ID均为1）。

需求 #

需要将每条记录的ValueAmount与**上周同分组对应记录的ValueAmount**并列显示。补充说明：同一周内可能存在多条同Team、Task、Username的记录，需为每条记录匹配对应上周的同分组值。

尝试的SQL及问题 #

曾尝试使用LAG函数，但仅返回上一行数据而非上周数据：

SELECT
    UserName, Team, ID, [DateEntered], [Task], T_ID, ValueAmount,
    LAG(ValueAmount) OVER (ORDER BY DateEntered) AS PreviousWeek,
    DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) - 7, '17530101') StartPW,
    DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) , '17530101') EndPW,
    ROW_NUMBER() OVER (PARTITION BY [T_ID] ORDER BY DateEntered DESC) AS rn
FROM
    Table

解决方案 #

方案1：使用LAG窗口函数结合分组匹配 #

通过CTE计算每条记录的周标识，再用LAG按分组字段+周排序，精准匹配上周同分组数据：

WITH WeeklyData AS (
    SELECT 
        Username, 
        Team, 
        ID, 
        DateEntered, 
        Task, 
        T_ID, 
        ValueAmount,
        -- 计算当前记录所属周的起始日期（沿用原有周计算逻辑）
        DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7), '17530101') AS CurrentWeekStart,
        -- 计算上周起始日期
        DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7) - 7, '17530101') AS LastWeekStart
    FROM YourTable -- 替换为实际表名
)
SELECT 
    wd.Username,
    wd.Team,
    wd.ID,
    wd.DateEntered,
    wd.Task,
    wd.T_ID,
    wd.ValueAmount,
    -- 按T_ID、用户、团队、任务分组，取上周对应记录的ValueAmount
    LAG(wd.ValueAmount) OVER (
        PARTITION BY wd.T_ID, wd.Username, wd.Team, wd.Task 
        ORDER BY wd.CurrentWeekStart
    ) AS LastWeeks_ValueAmount,
    wd.CurrentWeekStart,
    wd.LastWeekStart
FROM WeeklyData wd
ORDER BY wd.T_ID, wd.Username, wd.CurrentWeekStart, wd.DateEntered;

逻辑说明：PARTITION BY限定仅在同一T_ID、Username、Team、Task组内匹配，ORDER BY CurrentWeekStart确保按周顺序取上周数据，而非单条记录的顺序。

方案2：按周聚合后关联（适合需要上周分组总值的场景） #

若需求是获取上周同分组的聚合值（如总和、平均值），可先按周+分组聚合，再与原始表关联：

WITH WeeklyAgg AS (
    SELECT 
        Username,
        Team,
        T_ID,
        Task,
        DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7), '17530101') AS WeekStart,
        SUM(ValueAmount) AS TotalValue -- 可替换为MAX/AVG等聚合函数
    FROM YourTable
    GROUP BY Username, Team, T_ID, Task, DATEADD(dd, ((DATEDIFF(dd, '17530101', DateEntered) / 7) * 7), '17530101')
)
SELECT 
    t.Username,
    t.Team,
    t.ID,
    t.DateEntered,
    t.Task,
    t.T_ID,
    t.ValueAmount,
    wa_last.TotalValue AS LastWeeks_ValueAmount,
    DATEADD(dd, ((DATEDIFF(dd, '17530101', t.DateEntered) / 7) * 7), '17530101') AS CurrentWeekStart
FROM YourTable t
LEFT JOIN WeeklyAgg wa_last 
    ON t.Username = wa_last.Username
    AND t.Team = wa_last.Team
    AND t.T_ID = wa_last.T_ID
    AND t.Task = wa_last.Task
    AND wa_last.WeekStart = DATEADD(dd, ((DATEDIFF(dd, '17530101', t.DateEntered) / 7) * 7) - 7, '17530101')
ORDER BY t.T_ID, t.Username, t.DateEntered;

逻辑说明：先对每周的同分组数据做聚合，再通过LEFT JOIN匹配上周的聚合结果，确保每条原始记录都能关联到上周的分组总值。

内容的提问来源于stack exchange，提问作者New2Programming