如何在关联且按日期排序的SQL查询中返回当前行与上一行

首先，你的核心问题在于子查询没有关联外层查询的当前班组ID，导致子查询返回的是所有符合日期条件的总工作量，而不是对应班组的前一日数据。下面给你两种可行的解决方案：

方案1：修改关联子查询（兼容多数数据库，包括Access） #

我们需要给外层的Spread_Crew表起一个别名，然后在子查询中明确关联这个别名的ID，确保子查询只返回当前班组的前一日数据。同时调整日期条件，确保查询的是前一日的工作量（如果你的[Report Date]是参数，替换成对应的日期逻辑即可）。

修改后的完整SQL如下：

SELECT 
    sc.Description,
    Sum(Abs(dp.Station_Number_Begin - dp.Station_Number_End)) AS [Feet Total],
    sc.Hourly_Employee_Count AS [Hourly],
    sc.Salary_Employee_Count AS [Salary],
    (sc.Hourly_Employee_Count + sc.Salary_Employee_Count)*10 AS [Weekly Hours],
    (Date() - sc.Actual_Start_Date) AS [Crew Days to Date],
    Round(([Feet Total]/ [Crew Days to Date]),0) AS [FT/Day],
    -- 关联子查询：只返回当前班组前一日的工作量
    (SELECT 
         Sum(Abs(dp_prev.Station_Number_Begin - dp_prev.Station_Number_End))
     FROM Daily_Progress dp_prev
     WHERE dp_prev.Spread_Crew_Id = sc.ID  -- 关键：关联外层的当前班组ID
       AND dp_prev.PROGRESS_DATE = Date() - 1)  -- 取前一日数据，可根据需求替换为[Report Date]-1
    AS [Previous Footage]
FROM Spread_Crew sc
LEFT JOIN Daily_Progress dp ON sc.ID = dp.Spread_Crew_Id
WHERE sc.Print_On_Daily_Report = True  -- 把HAVING改成WHERE更高效，因为是筛选分组前的数据
GROUP BY 
    sc.Description,
    sc.Hourly_Employee_Count,
    sc.Salary_Employee_Count,
    sc.Sort_Order,
    sc.Actual_Start_Date
ORDER BY sc.Sort_Order;

关键修改点： #

• 给外层表起别名sc和dp，让子查询可以明确关联当前班组的sc.ID
• 子查询不再重复Join Spread_Crew，直接通过dp_prev.Spread_Crew_Id = sc.ID关联当前班组
• 将原HAVING中的Print_On_Daily_Report=True移到WHERE子句，提升查询效率（因为分组前筛选比分组后更高效）

方案2：使用窗口函数（适用于支持LAG()的数据库，如SQL Server、PostgreSQL等） #

如果你的数据库支持窗口函数，用LAG()函数可以更简洁地获取上一行数据，无需子查询：

WITH CrewDailyStats AS (
    SELECT 
        sc.ID,
        sc.Description,
        sc.Hourly_Employee_Count,
        sc.Salary_Employee_Count,
        sc.Actual_Start_Date,
        sc.Sort_Order,
        Sum(Abs(dp.Station_Number_Begin - dp.Station_Number_End)) AS [Feet Total],
        dp.PROGRESS_DATE
    FROM Spread_Crew sc
    LEFT JOIN Daily_Progress dp ON sc.ID = dp.Spread_Crew_Id
    WHERE sc.Print_On_Daily_Report = True
    GROUP BY 
        sc.ID, sc.Description, sc.Hourly_Employee_Count, sc.Salary_Employee_Count,
        sc.Actual_Start_Date, sc.Sort_Order, dp.PROGRESS_DATE
)
SELECT 
    Description,
    [Feet Total],
    Hourly_Employee_Count AS [Hourly],
    Salary_Employee_Count AS [Salary],
    (Hourly_Employee_Count + Salary_Employee_Count)*10 AS [Weekly Hours],
    (Date() - Actual_Start_Date) AS [Crew Days to Date],
    Round(([Feet Total]/ [Crew Days to Date]),0) AS [FT/Day],
    -- 按班组分区，按日期排序，获取上一日的工作量
    LAG([Feet Total], 1) OVER (PARTITION BY ID ORDER BY PROGRESS_DATE) AS [Previous Footage]
FROM CrewDailyStats
ORDER BY Sort_Order, PROGRESS_DATE;

优势： #

• 代码更简洁，逻辑更清晰
• 避免了子查询的重复计算，性能更优

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