join无法列转行因其不生成新行，只能拼接现有行；正确做法是union all拆列，再用join补全缺失科目。
为什么直接用 JOIN 做列转行会出错
因为 JOIN 本身不改变行数结构，它只是按条件拼接已有行。想把宽表（如 user_id, name, score_math, score_english）变成长表（user_id, subject, score），靠 JOIN 单独做不到——它没有“生成新行”的能力。常见误操作是写一堆 LEFT JOIN 到同一张子查询，结果得到笛卡尔积或 NULL 爆炸，数据量翻倍还查不出正确科目分布。
真正起作用的是 UNION ALL + JOIN 补全逻辑
列转行本质是“拆列成行”，必须靠 UNION ALL 把每个科目字段单独拎出来；而数据补全（比如补上某用户缺失的科目记录）才轮到 JOIN 出场：用完整科目维表左连实际成绩，再用 COALESCE 或默认值兜底。
- 先建一个科目维度表：
subjects(subject)，含 'math', 'english', 'physics' - 原始成绩表
student_scores含user_id,score_math,score_english,score_physics - 用
UNION ALL拆列：SELECT user_id, 'math' AS subject, score_math AS score FROM student_scores UNION ALL SELECT user_id, 'english', score_english FROM student_scores UNION ALL SELECT user_id, 'physics', score_physics FROM student_scores
- 再和
subjects左连接补全：SELECT s.user_id, sub.subject, COALESCE(t.score, 0) AS score FROM subjects sub CROSS JOIN (SELECT DISTINCT user_id FROM student_scores) s LEFT JOIN ( -- 上面的 UNION ALL 结果，建议物化为临时表或 CTE ) t ON t.user_id = s.user_id AND t.subject = sub.subject
MySQL 8.0+ 和 PostgreSQL 可用 LATERAL / JOIN LATERAL 简化写法
避免手写重复 UNION ALL，用行内子查询配合 LATERAL 更紧凑。但注意：MySQL 的 LATERAL 要求右侧子查询引用左侧列，且不能有聚合；PostgreSQL 支持更灵活。
- PostgreSQL 示例：
SELECT s.user_id, v.subject, v.score FROM student_scores s CROSS JOIN LATERAL ( VALUES ('math', s.score_math), ('english', s.score_english), ('physics', s.score_physics) ) AS v(subject, score) - 补全时仍需额外
JOIN到subjects表，否则只产出有数据的科目行 - SQL Server 用户请改用
UNPIVOT，但它的列名必须提前写死，不支持动态扩展
性能与 NULL 处理最容易被忽略
列转行后做 JOIN 补全，若原始成绩表很大，CROSS JOIN 会产生 用户数 × 科目数 的中间结果，极易 OOM。另外，score_math 为 NULL 时，UNION ALL 会保留 NULL 行，但补全后的 COALESCE(t.score, 0) 会掩盖“该用户确实没考”和“该用户考了但分数记为 NULL”的语义差异。
- 大表场景下，优先用 CTE 或临时表缓存
UNION ALL结果，避免重复计算 - 如果业务要求区分“未参考”和“缺分”，补全时别用
COALESCE，改用CASE WHEN t.score IS NULL THEN 'absent' ELSE 'scored' END - SQLite 不支持
LATERAL或UNPIVOT，只能硬写UNION ALL，且注意它对VALUES子句的支持有限制
列转行不是 JOIN 的主场，它是 UNION 的任务；JOIN 的价值在于补全、对齐、关联上下文——混淆这两者的职责，是多数人卡住的第一步。
