MySQL如何将Barcode表数据转换为指定行列格式的技术问询

问题背景 #

你有一张名为Barcode的MySQL表，结构和数据如下：

id | USER_CODE
1  | A1-123
2  | A2-456
3  | A3-123
4  | A4-789

已经通过以下SQL拆分出用户标识部分：

SELECT *, SUBSTRING_INDEX(`barcode`.`user_code`, '-', 1) AS `user_name` FROM barcode

执行后得到结果：

id | USER_CODE | user_name
1  | A1-123    | A1
2  | A2-456    | A2
3  | A3-123    | A3
4  | A4-789    | A4

现在需要将数据转换为如下行转列的格式：

code | A1  | A2  | A3  | A4
123  | DONE| DONE|     |
456  |     | DONE|     |
789  |     |     |     | DONE

实现思路与SQL代码 #

要实现这种行转列的效果，我们可以利用MySQL的条件聚合（CASE WHEN + 聚合函数）来完成，具体步骤如下：

1. 拆分出code字段：和拆分user_name逻辑类似，用SUBSTRING_INDEX(USER_CODE, '-', -1)获取横杠右侧的code值；
2. 按code分组：将同一个code的所有记录合并为一行；
3. 生成目标列：针对每个存在的user_name（A1、A2等），通过条件判断标记是否存在对应记录，存在则显示DONE，否则为空。

对应的SQL语句如下：

SELECT
    SUBSTRING_INDEX(USER_CODE, '-', -1) AS code,
    MAX(CASE WHEN SUBSTRING_INDEX(USER_CODE, '-', 1) = 'A1' THEN 'DONE' ELSE '' END) AS A1,
    MAX(CASE WHEN SUBSTRING_INDEX(USER_CODE, '-', 1) = 'A2' THEN 'DONE' ELSE '' END) AS A2,
    MAX(CASE WHEN SUBSTRING_INDEX(USER_CODE, '-', 1) = 'A3' THEN 'DONE' ELSE '' END) AS A3,
    MAX(CASE WHEN SUBSTRING_INDEX(USER_CODE, '-', 1) = 'A4' THEN 'DONE' ELSE '' END) AS A4
FROM Barcode
GROUP BY SUBSTRING_INDEX(USER_CODE, '-', -1);

代码细节解释 #

• SUBSTRING_INDEX(USER_CODE, '-', -1)：从右往左截取USER_CODE中横杠后的部分，得到我们需要的code列；
• CASE WHEN ... THEN 'DONE' ELSE '' END：判断当前行的用户标识是否匹配目标列（比如A1），匹配则返回DONE，否则返回空字符串；
• MAX()聚合函数：因为按code分组后，同一个code下可能对应多个用户标识，用MAX可以确保只要有匹配的记录，就会保留DONE值（空字符串和DONE取最大值会得到DONE）；
• GROUP BY：按拆分后的code分组，保证每个code在结果中只显示一行。

动态列的扩展方案 #

如果你的用户标识（A1、A2等）是动态新增的，不能提前写死在SQL里，可以用动态SQL自动生成列。具体实现如下：

SET @sql = NULL;
-- 拼接所有用户标识对应的CASE语句块
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN SUBSTRING_INDEX(USER_CODE, ''-'', 1) = ''',
            SUBSTRING_INDEX(USER_CODE, '-', 1),
            ''' THEN ''DONE'' ELSE '''' END) AS `',
            SUBSTRING_INDEX(USER_CODE, '-', 1),
            '`'
        )
    ) INTO @sql
FROM Barcode;

-- 拼接完整的SQL语句
SET @sql = CONCAT('SELECT SUBSTRING_INDEX(USER_CODE, ''-'', -1) AS code, ', @sql, ' FROM Barcode GROUP BY SUBSTRING_INDEX(USER_CODE, ''-'', -1)');

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

这个方案会自动读取表中所有不重复的用户标识，生成对应的列，不需要手动修改SQL，适配性更强。

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