嘗試獲取所有提到的賬戶（共 9 個）的輸出，即使沒有現有記錄GB.periode = 11
嘗試使用ISNULL(SUM(GB.bdr_val), 0)，但我仍然只得到GB.reknr = 5210 OR GB.reknr = 5211 OR GB.reknr = 5250 OR GB.reknr = 5340.
SELECT
SUM(GB.bdr_val) AS Total_Material_M,
GR.oms25_0 AS Desc2, GB.reknr AS Account
FROM
[100].[dbo].[gbkmut] GB
INNER JOIN
[100].[dbo].[grtbk] GR ON GB.reknr = GR.reknr
WHERE
GB.bkjrcode = 2021
AND GB.periode = 11
AND (GB.reknr = 5000 OR GB.reknr = 5050 OR GB.reknr = 5150 OR
GB.reknr = 5200 OR GB.reknr = 5210 OR GB.reknr = 5211 OR
GB.reknr = 5250 OR GB.reknr = 5340 OR GB.reknr = 5341)
GROUP BY
GR.oms25_0,GB.reknr
ORDER BY
GB.reknr ASC
我在下面有一個示例資料庫布局，
CREATE TABLE gbkmut
(
id INT,
reknr INT,
bdr_val INT,
bkjrcode INT,
periode INT
);
INSERT INTO gbkmut VALUES (1, 5210, 3511, 2021, 11);
INSERT INTO gbkmut VALUES (2, 5211, -40, 2021, 11);
INSERT INTO gbkmut VALUES (3, 5250, 33832, 2021, 11);
INSERT INTO gbkmut VALUES (3, 5340, -16, 2021, 11);
CREATE TABLE grtbk
(
id INT,
reknr INT,
oms25_0 varchar(60)
);
INSERT INTO grtbk VALUES (1, 5210, 'Description 1');
INSERT INTO grtbk VALUES (2, 5211, 'Description 2');
INSERT INTO grtbk VALUES (3, 5250, 'Description 3');
INSERT INTO grtbk VALUES (4, 5340, 'Description 4');
INSERT INTO grtbk VALUES (5, 5000, 'Description 5');
INSERT INTO grtbk VALUES (6, 5050, 'Description 6');
INSERT INTO grtbk VALUES (7, 5150, 'Description 7');
INSERT INTO grtbk VALUES (8, 5200, 'Description 8');
INSERT INTO grtbk VALUES (9, 5341, 'Description 9');
我仍然需要查詢輸出來顯示所有Total_Material_M,Desc2和Account列來顯示，即使值是0針對特定bkjrcode和periode值組合的，并且SUMS，不確定我在這里做錯了什么。
uj5u.com熱心網友回復：
如果我理解正確，您正在尋找OUTER JOIN,INNER JOIN將回傳在兩個表上的所有條件之間匹配的行。
對于這個下面的查詢RIGHT JOIN將回傳基于[dbo].[grtbk]偶數條件不匹配的行，但是行將是NULL
當行不匹配條件時GB.bdr_val，所以我們需要ISNULL在聚合函式中使用。
SELECT
SUM(ISNULL(GB.bdr_val,0)) AS Total_Material_M,
GR.oms25_0 AS Desc2,
GR.reknr AS Account
FROM
[dbo].[gbkmut] GB
RIGHT JOIN
[dbo].[grtbk] GR ON GB.reknr = GR.reknr
AND GB.bkjrcode = 2021
AND GB.periode = 11
GROUP BY
GR.oms25_0, GR.reknr
ORDER BY
GR.reknr ASC
uj5u.com熱心網友回復：
您需要切換連接的順序并將其更改為LEFT JOIN. 您還需要將條件移動GB到ON子句中。
SELECT
SUM(GB.bdr_val) AS Total_Material_M,
GR.oms25_0 AS Desc2, GB.reknr AS Account
FROM
dbo.grtbk GR
LEFT JOIN
dbo.gbkmut GB ON GB.reknr = GR.reknr
AND GB.bkjrcode = 2021
AND GB.periode = 11
AND (GB.reknr = 5000 OR GB.reknr = 5050 OR GB.reknr = 5150 OR
GB.reknr = 5200 OR GB.reknr = 5210 OR GB.reknr = 5211 OR
GB.reknr = 5250 OR GB.reknr = 5340 OR GB.reknr = 5341)
GROUP BY
GR.oms25_0,
GB.reknr
ORDER BY
GB.reknr ASC;
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425375.html