如何用SQL实现两张表关联并生成指定格式的统计结果？

现有表结构与数据 #

Table1（用户测试记录表） #

Table2（编码映射表） #

需求说明 #

需要将两表关联，生成以Table2的String为行、Table1的Name为列的统计结果表，统计每个用户对应编码的出现次数，最终结果如下：

SQL实现方案 #

思路解析 #

1. 行转列拆分测试记录：把Table1中每个用户的Test1、Test2两列数据拆成独立行，方便统一统计；
2. 关联映射表：将拆分后的编码与Table2关联，得到对应的String值；
3. 分组统计次数：按String和用户Name分组，统计每个用户对应编码的出现次数；
4. 列转行生成结果：将统计结果转换为以用户名为列的格式，补全未出现的编码对应的0值。

静态SQL实现（适用于用户列表固定的场景） #

WITH user_test_codes AS (
    -- 拆分Table1的两列测试编码为行数据
    SELECT Name, Test1 AS code FROM Table1
    UNION ALL
    SELECT Name, Test2 AS code FROM Table1
),
code_string_stats AS (
    -- 关联映射表并统计次数，RIGHT JOIN保证Table2的所有编码都被保留
    SELECT 
        t2.String,
        utc.Name,
        COUNT(*) AS count
    FROM user_test_codes utc
    RIGHT JOIN Table2 t2 ON utc.code = t2.code
    GROUP BY t2.String, utc.Name
)
-- 列转行生成最终结果，用COALESCE处理NULL为0
SELECT
    String AS Name,
    COALESCE(SUM(CASE WHEN Name = 'Tom' THEN count ELSE 0 END), 0) AS Tom,
    COALESCE(SUM(CASE WHEN Name = 'Mary' THEN count ELSE 0 END), 0) AS Mary,
    COALESCE(SUM(CASE WHEN Name = 'Mike' THEN count ELSE 0 END), 0) AS Mike,
    COALESCE(SUM(CASE WHEN Name = 'Amy' THEN count ELSE 0 END), 0) AS Amy
FROM code_string_stats
GROUP BY String
ORDER BY String;

动态SQL实现（适用于用户列表不固定的场景，以MySQL为例） #

如果用户列表可能动态变化，可以用动态SQL自动生成列：

-- 自动生成用户列的统计逻辑
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('COALESCE(SUM(CASE WHEN Name = ''', Name, ''' THEN count ELSE 0 END), 0) AS ', Name))
INTO @cols
FROM Table1;

-- 拼接完整SQL语句
SET @sql = CONCAT('
WITH user_test_codes AS (
    SELECT Name, Test1 AS code FROM Table1
    UNION ALL
    SELECT Name, Test2 AS code FROM Table1
),
code_string_stats AS (
    SELECT 
        t2.String,
        utc.Name,
        COUNT(*) AS count
    FROM user_test_codes utc
    RIGHT JOIN Table2 t2 ON utc.code = t2.code
    GROUP BY t2.String, utc.Name
)
SELECT String AS Name, ', @cols, ' 
FROM code_string_stats
GROUP BY String
ORDER BY String;
');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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