MySQL 8.0.27：提取多语言列去重值并整理为指定整洁格式

你需要的是把四个语言列的非空去重值拆分为每行仅一列有值的结果，用UNION ALL分别查询每列再合并即可实现：

-- 提取French列非空去重值，其他列置空
SELECT NULL AS number_id, NULL AS product, french, NULL AS german, NULL AS english, NULL AS italian
FROM egsmaster
WHERE french IS NOT NULL
GROUP BY french

UNION ALL

-- 提取German列非空去重值，其他列置空
SELECT NULL AS number_id, NULL AS product, NULL AS french, german, NULL AS english, NULL AS italian
FROM egsmaster
WHERE german IS NOT NULL
GROUP BY german

UNION ALL

-- 提取English列非空去重值，其他列置空
SELECT NULL AS number_id, NULL AS product, NULL AS french, NULL AS german, english, NULL AS italian
FROM egsmaster
WHERE english IS NOT NULL
GROUP BY english

UNION ALL

-- 提取Italian列非空去重值，其他列置空
SELECT NULL AS number_id, NULL AS product, NULL AS french, NULL AS german, NULL AS english, italian
FROM egsmaster
WHERE italian IS NOT NULL
GROUP BY italian;

代码说明 #

• 每个SELECT语句单独处理一个语言列，通过WHERE 列名 IS NOT NULL过滤空值，GROUP BY实现去重（也可以替换成SELECT DISTINCT，效果完全一致）
• 非目标列统一设为NULL，确保每行只有对应语言列有值，完全符合你要求的格式
• UNION ALL将四个独立的结果集合并，得到所有语言的去重值集合

导出到Excel #

在MySQL Workbench执行完上述查询后：

1. 点击结果集面板顶部的导出图标（带箭头的方框样式）
2. 选择导出格式为Excel (*.xlsx)
3. 按照向导设置保存路径和文件名，完成导出即可得到整洁的表格样式

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