使用下一行的日期(-1天)作为当前行的结束日期。

Question

我试图在当前脚本中自加入，以便找到下一行，然后无论哪一天指定，它都应该从它减去1天，并将其放在当前行的“结束日期”列中，但我似乎在哪里出错了。

SELECT 
    BCG.BudgetId
    ,B.CustomerId
    ,CAST(BCG.StartOfPeriod AS DATE) AS StartOfPeriod
    ,BCG2.EndOfPeriod
    ,ROUND(SUM(BCG.Charge),2) AS ExpenditureBudget
    ,ROUND(SUM(BCG.Consumption),2) AS ConsumptionBudget
    ,ROW_NUMBER() OVER (PARTITION BY BCG.BudgetId ORDER BY BCG.StartOfPeriod ASC) AS rowNum
    ,B.Status
FROM Budgets_BudgetCalcGroup BCG 
INNER JOIN Budgets_Budget B ON B.Id = BCG.BudgetId
LEFT JOIN Budgets_BudgetCalcGroup BCG2 ON 
        BCG2.EndOfPeriod = (SELECT MIN(StartOfPeriod)-1
                            FROM Budgets_BudgetCalcGroup AS t3
                            WHERE t3.StartOfPeriod > t1.StartOfPeriod
                            )
WHERE B.Status = 2 
GROUP BY BCG.BudgetId,StartOfPeriod

收到的错误：

Unknown Column BCG2.EndOfPeriod in field list

预期产出：

254 41  2018-09-01   2018-09-30  29017.8    542331.59   1   2
254 41  2018-10-01   2018-10-31  27858.82   575545.97   2   2
254 41  2018-11-01   2018-11-30  28927.71   576106.15   3   2
254 41  2018-12-01   NULL        34639.71   613779.57   4   2

Answer 1

除了使用LEAD()函数的自联接之外，我还找到了另一种方法。

DATE_ADD(CAST(LEAD(BCG.StartOfPeriod, 1) OVER (PARTITION BY BCG.BudgetId ORDER BY BCG.StartOfPeriod) AS DATE),INTERVAL -1 DAY) AS EndOfPeriod

输出：

254 41  2018-09-01  2018-09-30  29017.8     542331.59   1
254 41  2018-10-01  2018-10-31  27858.82    575545.97   2
254 41  2018-11-01  2018-11-30  28927.71    576106.15   3
254 41  2018-12-01  2018-12-31  34639.71    613779.57   4