我有一個查詢，其中包含列“Cremembers.Name”（這從表“Crewmembers”中提取“Name”列）和“Sum of HoursUW”（這對每個“Crewmember.Name”的不同表“Marine391”中的“HoursUW”求和"。Cremembers.Name 和 Marine391.Crewmembers 之間存在現有關系，其中列出了所有 Cremembers.Name 值，并且僅列出了來自 Marine391.Crewmembers 的值，其中連接欄位相等。
在此查詢中，如果機組成員沒有任何 HoursUW，我希望“Sum of HoursUW”的默認值為 0。
這是當前的 SQL 代碼：
SELECT DISTINCTROW Crewmembers.Name, Sum(Marine391_29ft_SAFEBOAT.HoursUW) AS [Sum Of HoursUW]
FROM Crewmembers LEFT JOIN Marine391_29ft_SAFEBOAT ON Crewmembers.[Name] = Marine391_29ft_SAFEBOAT.[Crewmembers].[Value]
GROUP BY Crewmembers.Name;
我嘗試像這樣使用 nz() 函式：
nz((SELECT DISTINCTROW Crewmembers.Name, Sum(Marine391_29ft_SAFEBOAT.HoursUW), 0) AS [Sum Of HoursUW]
FROM Crewmembers LEFT JOIN Marine391_29ft_SAFEBOAT ON Crewmembers.[Name] = Marine391_29ft_SAFEBOAT.[Crewmembers].[Value]
GROUP BY Crewmembers.Name;
像這樣：
SELECT DISTINCTROW Crewmembers.Name, nz(Sum(Marine391_29ft_SAFEBOAT.HoursUW), 0) AS [Sum Of HoursUW]
FROM Crewmembers LEFT JOIN Marine391_29ft_SAFEBOAT ON Crewmembers.[Name] = Marine391_29ft_SAFEBOAT.[Crewmembers].[Value]
GROUP BY Crewmembers.Name;
這些都有語法錯誤。
如何使用 nz() 函式，或者有沒有更好的方法在這個查詢中將默認值設定為 0？
提前致謝！！！
uj5u.com熱心網友回復：
Nz()直接應用到HoursUW你的領域Sum()。
SELECT cm.Name, Sum(Nz(msb.HoursUW, 0)) AS [Sum Of HoursUW]
FROM
Crewmembers AS cm
LEFT JOIN Marine391_29ft_SAFEBOAT AS msb
ON cm.[Name] = msb.[Crewmembers].[Value]
GROUP BY cm.Name;
我沒有包括在內DISTINCTROW，因為查詢的結果集行由于GROUP By.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/514548.html