按列计算MySQL中NULL内容，按列分组

Question

如何按字段/列计算非空条目？我看到有几个答案可以按行计数，但不知道如何为列进行操作。

输入：

╔════╦════════╦════════╦════════╗
║ id ║ field1 ║ field2 ║ field3 ║
║ 1  ║ do     ║ re     ║ me     ║
║ 2  ║ fa     ║        ║ so     ║
║ 3  ║ la     ║ te     ║        ║
║ 4  ║ da     ║ re     ║        ║
╚════╩════════╩════════╩════════╝

输出：

id       4
field1   4
field2   3
field3   2

我试图了解一个非常混乱的数据库中字段的使用情况，因为这个数据库中有大约50个列，所以我正在寻找一种不需要输入每个列名的方法。

由于数据存储的不一致性，我可能还需要扩展搜索到非NULL和非空且≠ 0和≠“no”的内容 - 一些字段从未被使用，但自动填充为“no”。

这个答案看起来接近我所需的内容，但会生成SQL错误，而我没有足够的声望来评论：在SQL中计算每个列中NULL值的数量。

- Slam

---

更新：如果有区别，我使用的是MySQL而不是SQL。 - Slam

Answer 1

只需使用count()：

select count(field1), count(field2), count(field3)
from table t;

count()是用来计算非空值的函数。

如果您不想手动输入列名，那么可以使用元数据表（通常是information_schema.columns）获取列名。您可以将生成的SQL作为查询语句，或者将列名复制到电子表格中以生成代码。

编辑：

您可以使用以下方法生成代码：

select group_concat('count(', column_name, ')' separate ', ')
from information_schema.columns
where table_name = <whatever> and table_schema = <whatever2>;

请注意，这里使用了 group_concat() 函数的一个鲜为人知的特性——它可以接受多个字符串参数。

- Gordon Linoff

---

@gordon linoff，我认为用户想要逐行数据，而不是逐列。 - Ankit Bajpai

当我运行这个程序时，我遇到了一个SQL语法错误。我应该在哪里用我的表名替换“table t”？另外，由于有50个列名，我正在寻找一种编程方式来运行此程序，而不需要手动输入每个名称。 - Slam

如果您不喜欢输入名称，那么可以使用元数据表（通常是information_schema.columns）获取列名。您可以将 SQL 作为查询生成... 我知道如何使用count()函数。我现在缺失的是通过SQL查询获取列名的方法，请问您能否修改您的答案并包含此内容？ - Slam

@Gordon的答案最终是正确的，当时我太新了，无法理解它。并且使用SQL输出新的查询语言的元回答值得赞赏。 - Slam

Answer 2

SELECT count(field1) as cnt ,'field1' as field from tbl where field1 IS NOT NULL
UNION all
SELECT count(field2) as cnt ,'field2' as field from tbl where field2 IS NOT NULL
union all 
SELECT count(field3) as cnt ,'field3' as field from tbl where field3 IS NOT NULL

如果只有三个固定字段，以上SQL语句将可以正常工作。

- sumit

---

大约有50个字段。如果我想要手动输入它们，这是可行的。 - Slam

除非有相同的列名，否则您实际上并不需要使用 UNION ALL，这在实际情况中是不可能的。 - Salman A

Answer 3

这里是sqlfiddle。

SELECT
  COUNT(id),
  SUM(CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END), 
  SUM(CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END), 
  SUM(CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END)
FROM table1;

- Alex