统计MySQL中的空数据

Question

假设我有一张表，其中包含如下数据：

name       status
bob        single
bob        single
jane       null
tina       null
shane      married

我想要如果状态“单一或数据为空”则表示单一。因此，如果数据为空，脚本可以将其读取为单个数据，并且可以一起计数。 所以我可以显示如下结果：

Single     Married
3            1

我已经尝试过这个，但它不起作用：

SELECT SUM(IF(status='single',1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

Answer 1

使用：

SELECT SUM(CASE WHEN x.status = 'single' OR x.status IS NULL THEN 1 ELSE 0 END) AS single,
       SUM(CASE WHEN x.status = 'married' THEN 1 ELSE 0 END) AS married
  FROM (SELECT DISTINCT
               t.name,
               t.status
          FROM YOUR_TABLE t) x

Answer 2

如果您知道只有“单身”、“已婚”和“空”作为选项，那么这将起作用：

SELECT SUM(IF(status!='married',1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

否则请尝试

SELECT SUM(IF(status='single' OR status is null,1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

Answer 3

SELECT SUM(IF(status='single' OR status IS NULL,1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table