我正在嘗試自行加入我當前的腳本以查找下一行，然后無論指定哪一天，它都應該減去 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
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/475529.html
上一篇：用時刻js在作業日查找第n周重復