通常,我建议coalesce
将空值自动变形为其他值,但是MsAccess似乎没有此值。您可以尝试nz
:
select nz(user, '<<NULL>>') as user,
count(*) as count
from tests
group by user
反而。
您可能还会发现,完全摆脱每行函数和union
两个单独的查询的效率更高(当然这取决于您的DBMS),例如:
select user as user, count(*) as count
from tests
where user is not null
group by user
union all select '<<NULL>>' as user, count(*) as count
from tests
where user is null
但是我不知道MsAccess是否允许这样做(或者您是否在乎)。我知道它很可能在我使用的主要DBMS上表现更好,但是您应该始终记住: 测量,不要猜测!