如何根据另一表多列的唯一值统计并更新单列？SQL求助

需求：将table1的result列更新为table2中对应header列的唯一值数量。

现有table2结构及数据：

table1结构：

尝试执行以下SQL时出现错误：

UPDATE table1 
SET result = (SELECT COUNT(*) 
              FROM 
                  (SELECT DISTINCT 
                       (SELECT header FROM table1) 
                   FROM table2) AS dists);

1. 子查询(SELECT header FROM table1)会返回table1中所有header值（多行结果），无法作为DISTINCT的单个字段使用，数据库会抛出“子查询返回多行”的错误。
2. 语句逻辑未关联table1的当前行header值，即便不报错，所有行的result也会被设置为同一个错误值，无法实现“对应列唯一值数量”的需求。

由于需要根据table1的header值动态匹配table2的列，分两种场景处理：

场景1：静态列（已知所有列名） #

如果table2的列固定，可以使用关联子查询结合CASE语句逐个匹配列名：

UPDATE table1 t1
SET result = (
    SELECT COUNT(DISTINCT col)
    FROM (
        SELECT 
            CASE t1.header
                WHEN 'a' THEN a
                WHEN 'b' THEN b
                WHEN 'c' THEN c
                WHEN 'd' THEN d
                WHEN 'e' THEN e
                WHEN 'f' THEN f
                WHEN 'g' THEN g
                WHEN 'h' THEN h
                WHEN 'i' THEN i
                WHEN 'j' THEN j
                WHEN 'k' THEN k
                WHEN 'l' THEN l
                WHEN 'm' THEN m
                WHEN 'n' THEN n
                WHEN 'o' THEN o
                WHEN 'p' THEN p
                WHEN 'q' THEN q
                WHEN 'r' THEN r
                WHEN 's' THEN s
                WHEN 't' THEN t
                WHEN 'u' THEN u
                WHEN 'v' THEN v
                WHEN 'w' THEN w
                WHEN 'x' THEN x
                WHEN 'y' THEN y
                WHEN 'z' THEN z
                WHEN 'aa' THEN aa
                WHEN 'bb' THEN bb
                WHEN 'cc' THEN cc
            END AS col
        FROM table2
    ) AS temp
);

场景2：动态列（列名不固定或较多） #

如果使用支持动态SQL的数据库（如MySQL、SQL Server），可以生成并执行动态语句自动遍历所有列：

MySQL 版本 #

SET @sql = '';
SELECT GROUP_CONCAT(
    CONCAT(
        "UPDATE table1 SET result = (SELECT COUNT(DISTINCT ", column_name, ") FROM table2) WHERE header = '", column_name, "';"
    )
) INTO @sql
FROM information_schema.columns
WHERE table_name = 'table2';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 版本 #

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += CONCAT(
    "UPDATE table1 SET result = (SELECT COUNT(DISTINCT ", QUOTENAME(column_name), ") FROM table2) WHERE header = '", column_name, "';"
)
FROM information_schema.columns
WHERE table_name = 'table2';

EXEC sp_executesql @sql;

执行正确语句后，table1的result列会被更新为对应table2列的唯一值数量，例如：

• header='a'的result为7（table2中a列的唯一值：8,10,12,14,15,18,20）
• header='b'的result为1（所有行b列值都是2）
• header='cc'的result为2（唯一值0和9）

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