mysql - 统计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