在 MySQL 中返回列名及其不同值的计数 [英] Return column names and count of their distinct values in MySQL

问题描述

我正在寻找一个动态 MySQL 查询，它会计算表的每一列中的不同值，并且还会告诉我哪些列包含 Null 值.
这是示例表，我使用 'db_name' 作为数据库名称和 'table_name' 作为表名:

I am looking for a dynamic MySQL query which would count the distinct values in each column of a table and which would also tell me which of the columns contain Null values.
Here is the sample table, I used 'db_name' as database name and 'table_name' as table name:

+------+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 | Col5 |
+------+------+------+------+------+
| a    | d    | j    | o    | q    |
| b    | e    | k    | o    | r    |
| c    | f    | l    | o    | NULL |
| a    | g    | NULL | p    | t    |
| b    | h    | m    | NULL | r    |
| a    | i    | n    | p    | s    |
+------+------+------+------+------+

这是我想要得到的结果集:

This is the result set that I would like to get:

+----------+---------------+---------------+
| Col Name | Unique values | Contains Null |
+----------+---------------+---------------+
| Col1     |             3 | FALSE         |
| Col2     |             6 | FALSE         |
| Col3     |             6 | TRUE          |
| Col4     |             3 | TRUE          |
| Col5     |             5 | TRUE          |
+----------+---------------+---------------+

到目前为止，这是我设法弄清楚的:

So far this is what I have managed to figure out:

-- list all the column names

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name';

-- count the distinct values in a column

SELECT COUNT(DISTINCT Col1) Col1 
FROM table_name;

-- tell if a column contains any Null

SELECT
  (CASE WHEN (SUM(CASE WHEN Col1 IS NULL THEN 1 ELSE 0 END)) > 0 THEN 'TRUE' 
  ELSE 'FALSE' END) 'Contains Null'
FROM table_name;

-- combining the queries

SELECT
  (SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'Col1') 'Col Name', 
  (SELECT COUNT(DISTINCT Col1) 
  FROM table_name) 'Unique values',
  (SELECT (CASE WHEN (SUM(CASE WHEN Col1 IS 
  NULL THEN 1 ELSE 0 END)) > 0 THEN 'TRUE' ELSE 'FALSE' END)
  FROM table_name) 'Contains Null';

现在，我假设我需要构建一个循环来遍历每一列并统一查询返回的记录或将它们插入到新表中.问题是，我对 SQL 比较陌生，而且我还不太熟悉循环和变量.

Now, I assume I would need to build a loop that goes through each column and unifies the records returned by the query or inserts them into a new table. The problem is, I am relatively new to SQL and I am not really familiar with loops and variables yet.

我发现了一些与我相似的问题，但没有一个给我明确的答案:

I found a few questions similar to mine, but none of them gave me a clear answer:

SQL Server 计数表格每列中不同值的数量

返回列名和不同值

SQL:计算每列中不同值的数量

推荐答案

我只需要类似于这个问题的东西(获取所有表不同值的计数以及仅使用 SQL 从任何表中获取它的简单方法)所以我以这种方式做到了.希望它可以在这样的情况下帮助某人.

I just needed thing similiar to this question (get count of all table distinct values and easy way to get it from any table using only SQL) so I've made it on this way. Hopefully it may help somebody in cases like this one.

SET @processedtable := 'myprecioustablename';
SET @columnnames := (
    SELECT GROUP_CONCAT(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @processedtable);
SET @qrypartcount := REPLACE(@columnnames, ',','), COUNT(DISTINCT ');
SET @validquery := CONCAT("SELECT COUNT(DISTINCT ", @qrypartcount, ") FROM ", @processedtable);
PREPARE stmt FROM @validquery;
EXECUTE stmt;

这篇关于在 MySQL 中返回列名及其不同值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！