如何获取具有5个及以上不同值的列的元数据？

你的原查询思路方向没错，但有两个关键问题：

1. 没法直接用TABLE_SCHEMA + TABLE_NAME拼接成表名在静态SQL里使用，SQL不支持这种动态引用对象名的方式；
2. 子查询里的count(distinct COLUMN_NAME)是统计列的数量，不是某一列的不同值数量，逻辑搞反了。

下面给两种可行的方案：

方案一：逐个表手动查询（适合表数量少的场景） #

因为你只需要查3个表，直接针对每个表的列分别统计不同值数量，再筛选符合条件的结果：

-- 统计TABLE_1的列
SELECT 
    'TABLE_1' AS TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    DISTINCT_COUNT
FROM (
    SELECT 
        'COLUMN_1' AS COLUMN_NAME,
        'INT' AS DATA_TYPE, -- 替换成该列实际的数据类型
        COUNT(DISTINCT COLUMN_1) AS DISTINCT_COUNT
    FROM TABLE_1
    UNION ALL
    SELECT 
        'COLUMN_2' AS COLUMN_NAME,
        'VARCHAR(50)' AS DATA_TYPE, -- 替换成实际类型
        COUNT(DISTINCT COLUMN_2) AS DISTINCT_COUNT
    FROM TABLE_1
    -- 把TABLE_1的所有列都按这个格式加进来
) t
WHERE DISTINCT_COUNT >=5

UNION ALL

-- 统计TABLE_2的列，写法和上面一致
SELECT 
    'TABLE_2' AS TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    DISTINCT_COUNT
FROM (
    SELECT 
        'COLUMN_A' AS COLUMN_NAME,
        'DATE' AS DATA_TYPE,
        COUNT(DISTINCT COLUMN_A) AS DISTINCT_COUNT
    FROM TABLE_2
    UNION ALL
    SELECT 
        'COLUMN_B' AS COLUMN_NAME,
        'DECIMAL(10,2)' AS DATA_TYPE,
        COUNT(DISTINCT COLUMN_B) AS DISTINCT_COUNT
    FROM TABLE_2
) t
WHERE DISTINCT_COUNT >=5

UNION ALL

-- 统计TABLE_3的列，同理
SELECT 
    'TABLE_3' AS TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    DISTINCT_COUNT
FROM (
    SELECT 
        'COLUMN_X' AS COLUMN_NAME,
        'VARCHAR(100)' AS DATA_TYPE,
        COUNT(DISTINCT COLUMN_X) AS DISTINCT_COUNT
    FROM TABLE_3
    UNION ALL
    SELECT 
        'COLUMN_Y' AS COLUMN_NAME,
        'INT' AS DATA_TYPE,
        COUNT(DISTINCT COLUMN_Y) AS DISTINCT_COUNT
    FROM TABLE_3
) t
WHERE DISTINCT_COUNT >=5

方案二：用动态SQL自动生成查询（适合表/列多的场景） #

如果表或列数量多，手动写太麻烦，可以用动态SQL自动生成统计语句。下面以MySQL为例：

-- 定义要查询的库和表
SET @target_schema = 'MY_SCHEMA';
SET @target_tables = 'TABLE_1,TABLE_2,TABLE_3';

-- 自动生成统计每个列的SQL语句
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ''', TABLE_NAME, ''' AS TABLE_NAME, ''', COLUMN_NAME, ''' AS COLUMN_NAME, ''', DATA_TYPE, ''' AS DATA_TYPE, COUNT(DISTINCT `', COLUMN_NAME, '`) AS DISTINCT_COUNT FROM `', @target_schema, '`.`', TABLE_NAME, '` HAVING DISTINCT_COUNT >=5'
    ) SEPARATOR ' UNION ALL '
) INTO @dynamic_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_schema 
  AND FIND_IN_SET(TABLE_NAME, @target_tables);

-- 执行生成的SQL
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个方法会自动从INFORMATION_SCHEMA.COLUMNS里获取目标表的所有列信息，拼接成统计查询语句，最后一次性执行，返回所有符合条件的列。

注意：不同数据库的动态SQL语法不一样，比如SQL Server要用EXEC sp_executesql，PostgreSQL要用EXECUTE，需要根据你用的数据库调整。

内容的提问来源于stack exchange，提问作者Ville