在查询结果中，当值为Null时显示字符串 [英] In query result, when value is Null show string instead

问题描述

SELECT User, COUNT(*) as count FROM Tests GROUP by User;

这可能返回行，其中User为空，但count为某个数字.如何修改查询，这样我可以看到一些字符串而不是空(null)行?

This may return row, where User is null but count is some number. How can I modify query so instead of empty(null) row I can see some string?

推荐答案

通常，我建议coalesce将空值自动变形为其他值，但是MsAccess似乎没有此值.您可以尝试 nz :

Normally, I'd suggest coalesce to automatically morph null values to something else, but it appears MsAccess may not have this. You could try nz instead:

select     nz(user, '<<NULL>>') as user,
           count(*) as count
from       tests
group by   user

相反.

您可能还会发现，完全摆脱逐行函数和union两个独立查询的效率更高(当然这取决于您的DBMS)，例如:

You may also find that it's more efficient (this depends on your DBMS of course) to get rid of per-row functions altogether and union two separate queries, something like:

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上表现更好，但是您应该始终记住:测量，不要猜测！

But whether MsAccess allows this (or whether you care), I don't know. I know it will most likely perform better on the main DBMS I use but you should always remember: measure, don't guess!

这篇关于在查询结果中，当值为Null时显示字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！