设置查询列的默认值

Question

我有一个带列的查询"船员姓名；(这从表"Crewmembers"中拉出"Name"列(；小时总和UW"；(这是来自不同表"水手391"每个"船员姓名"的"小时UW"的总和。船员姓名和水手391之间存在现有关系。列出所有船员的船员姓名值，仅列出来自水手391的值。列出联合字段相等的船员。

在这个查询中，如果船员没有任何HoursUW，我想要默认值"；小时总和UW"；为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吗？

提前感谢！！！

Answer 1

像Sum()一样将Nz()直接应用于HoursUW字段。

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而不同了。