MySQL技术问询：如何基于另一张表的值进行关联查询

当然可以实现！咱们先理清楚核心逻辑：table1是按时间存储的宽表，每一行包含多个Count_x类型的列；table2是这些列名与业务名称的映射表。要实现基于table2的name值查询table1，关键是先把table1的宽表转成窄表（行转列），让每个Count_x列变成单独的一行，这样就能和table2的id列（对应列名）关联起来了。

下面分两种场景给你具体方案：

一、静态列场景（已知所有Count_x列名） #

如果你的Count_x列数量固定且不多（比如示例里的Count_0到Count_2），可以直接用UNION ALL手动实现行转列，再关联table2：

SELECT 
    t1.id,
    t1.date,
    t2.name,
    t1.count_value
FROM (
    -- 把每个Count_x列拆成单独的行，记录列名和对应的值
    SELECT id, date, 'Count_0' AS count_col, Count_0 AS count_value FROM table1
    UNION ALL
    SELECT id, date, 'Count_1' AS count_col, Count_1 AS count_value FROM table1
    UNION ALL
    SELECT id, date, 'Count_2' AS count_col, Count_2 AS count_value FROM table1
    -- 如果有Count_3到Count_9，继续添加对应的UNION ALL语句即可
) t1
-- 关联table2，用列名匹配table2的id
JOIN table2 t2 ON t1.count_col = t2.id
-- 可选：根据name筛选，比如只显示name不为空的记录
WHERE t2.name IS NOT NULL;

二、动态列场景（自动识别所有Count_x列） #

如果Count_x列数量较多（比如Count_0到Count_9甚至更多），手动写UNION ALL太繁琐，可以用动态SQL自动生成行转列的语句，适配所有Count_x列：

SET @sql = NULL;

-- 第一步：动态生成所有Count_x列的行转列语句
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT id, date, ''', column_name, ''' AS count_col, ', column_name, ' AS count_value FROM table1'
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.columns
WHERE table_schema = DATABASE() -- 当前数据库
  AND table_name = 'table1'
  AND column_name LIKE 'Count_%'; -- 匹配所有以Count_开头的列

-- 第二步：拼接完整的查询SQL，关联table2
SET @sql = CONCAT(
    'SELECT t1.id, t1.date, t2.name, t1.count_value FROM (',
    @sql,
    ') t1 JOIN table2 t2 ON t1.count_col = t2.id WHERE t2.name IS NOT NULL'
);

-- 第三步：执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个方案会自动读取table1中所有Count_开头的列，无需手动维护，适合列数量固定但较多的场景。

拓展：基于name做聚合统计 #

如果需要统计每个name对应的总计数，可以在关联后添加分组聚合：

SELECT 
    t2.name,
    SUM(t1.count_value) AS total_count,
    MAX(t1.date) AS latest_record_date
FROM (
    SELECT id, date, 'Count_0' AS count_col, Count_0 AS count_value FROM table1
    UNION ALL
    SELECT id, date, 'Count_1' AS count_col, Count_1 AS count_value FROM table1
    UNION ALL
    SELECT id, date, 'Count_2' AS count_col, Count_2 AS count_value FROM table1
) t1
JOIN table2 t2 ON t1.count_col = t2.id
WHERE t2.name IS NOT NULL
GROUP BY t2.name;

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