mysql 查询 not null_在mysql查询中同时列出null和not null

Let's say I have the table

NAME | ID | REF

foo1 | 1 | NULL

foo2 | 2 | 1234

foo2 | 3 | 567

foo1 | 4 | NULL

foo3 | 5 | 89

I'd like to count all instances of NULL and NOT NULL in one query so that I can say

NAME | null | not null

foo1 | 0 | 2

foo2 | 2 | 0

foo3 | 0 | 1

I could run these two queries

select NAME,count(*) from TABLE where REF is not null

select NAME,count(*) from TABLE where REF is null

But I'm sure there must be a simple way to do it in one mysql query.

解决方案

You can use SUM() on ISNULL() like this

select NAME, sum(isnull(REF)) as is_null, sum(not isnull(REF)) as is_not_null from TABLE group by NAME;

SUM(1) is equivalent to COUNT(*), so it will really make a count.