我有這樣的情況。出于某種原因，當我加入兩個臨時表時，一列會得到完全不同的結果。根據我是按實際資料還是預測中的 LOC 列分組，其中一列給出了正確的結果，而另一列給出了完全奇怪的結果。
結果對于 Actuals (1718789) 是準確的，但對于 Forecast 是錯誤的
WITH ACTUALS AS
(
SELECT [LOC], [DMDUNIT], [DMDPostDate],
SUM(HistoryQuantity) AS 'Actuals'
FROM SCPOMGR.HISTWIDE_CHAIN
GROUP BY [LOC], [DMDUNIT], [DMDPostDate]
),
Forecast AS
(
SELECT [LOC], [DMDUNIT], [STARTDATE],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
-- Forecast Albertsons 99484.136 (wrong: should be 122880.591)
GROUP BY [LOC], [DMDUNIT], [STARTDATE]
)
SELECT A.[LOC], SUM(A.Actuals) AS 'Actuals', SUM(F.Forecast) AS 'Forecast'
FROM Actuals A FULL OUTER JOIN Forecast F
on A.[DMDUNIT] = F.[DMDUNIT]
AND f.[STARTDATE] = a.[DMDPostDate]
and a.[LOC] = f.[LOC]
GROUP BY A.[LOC]
ORDER BY A.[LOC]
當我將 GROUP BY 從 A.[LOC] 更改為 F.[LOC] 時，我會產生相反的效果。現在我的 Actuals 是正確的，但 Forecast 不是。
GROUP BY 有問題。取決于我是否 GROUP BY a.[LOC] 我得到了“Actuals”的正確結果但“Forecast”不正確，如果我 GROUP BY f.[LOC] 我得到“Forecast”的正確結果，但“Actuals”不正確'。
WITH ACTUALS AS
(
SELECT [LOC], [DMDUNIT], [DMDPostDate],
SUM(HistoryQuantity) AS 'Actuals'
FROM SCPOMGR.HISTWIDE_CHAIN
-- Actuals 80324 (Albertsons) grouped by F.[LOC] - incorrect (should be 1718789)
GROUP BY [LOC], [DMDUNIT], [DMDPostDate]
),
Forecast AS
(
SELECT [LOC], [DMDUNIT], [STARTDATE],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
-- Forecast Albertsons 122880.591 (Albertsons) grouped by f.[LOC] - correct
GROUP BY [LOC], [DMDUNIT], [STARTDATE]
)
SELECT F.[LOC], SUM(F.Forecast) AS 'Forecast', SUM(A.Actuals) AS 'Actuals'
FROM Forecast F FULL OUTER JOIN Actuals A
on F.[DMDUNIT] = A.[DMDUNIT]
AND F.[STARTDATE] = A.[DMDPostDate]
and F.[LOC] = A.[LOC]
GROUP BY F.[LOC]
ORDER BY F.[LOC]
從技術上講，它是相同的代碼，只是 GROUP BY 拋出其中一列的結果。
有誰知道如何修復。我需要在這 3 個欄位上保留 JOIN
on A.[DMDUNIT] = F.[DMDUNIT]
AND f.[STARTDATE] = a.[DMDPostDate]
and a.[LOC] = f.[LOC]
uj5u.com熱心網友回復：
您使用的是 FULL OUTER JOIN，所以我假設兩個表之間會有不匹配的行，并且 LOC 有時在任一表中為 NULL。當一個表為 NULL 時，使用 COALESCE 從任一表中獲取值。
WITH ACTUALS AS
(
SELECT [LOC], [DMDUNIT], [DMDPostDate],
SUM(HistoryQuantity) AS 'Actuals'
FROM SCPOMGR.HISTWIDE_CHAIN
GROUP BY [LOC], [DMDUNIT], [DMDPostDate]
),
Forecast AS
(
SELECT [LOC], [DMDUNIT], [STARTDATE],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
-- Forecast Albertsons 99484.136 (wrong: should be 122880.591)
GROUP BY [LOC], [DMDUNIT], [STARTDATE]
)
SELECT COALESCE(a.[LOC], f.[LOC]) as LOC, SUM(A.Actuals) AS 'Actuals', SUM(F.Forecast) AS 'Forecast'
FROM Actuals A FULL OUTER JOIN Forecast F
on A.[DMDUNIT] = F.[DMDUNIT]
AND f.[STARTDATE] = a.[DMDPostDate]
and a.[LOC] = f.[LOC]
GROUP BY COALESCE(a.[LOC], f.[LOC])
ORDER BY LOC
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363707.html
標籤：sql sql-server 查询语句
上一篇：選擇只包含一個特定專案的一組行
下一篇：使用一個查詢的結果到另一個