如何将SQL表列名转行_使用UNPIVOT或UNION ALL操作-SQL-PHP中文网

UNPIVOT 比 UNION ALL 更干净，因其原生支持列转行、逻辑集中、自动过滤 NULL 且提前校验结构；而 UNION ALL 易出错、需手动对齐字段、重复扫描且顺序不可控。

如何将sql表列名转行_使用unpivot或union all操作

UNPIVOT 为什么比 UNION ALL 更干净

UNPIVOT 是 SQL Server 和部分数据库（如 Oracle）原生支持的列转行操作，它把多个列一次性“摊平”成两列：一列存原列名，一列存对应值。相比手写一堆 UNION ALL，它更少出错、逻辑更集中，也更容易维护。

常见错误现象：UNION ALL 每个子查询必须字段数、类型、顺序完全一致，稍有不匹配就报 all queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists；而 UNPIVOT 在定义阶段就强制约束列结构，提前暴露问题。

只适用于固定列数场景 —— 列名必须写死在 UNPIVOT 子句里，无法动态生成
UNPIVOT 会自动过滤 NULL 值（除非你显式保留），而 UNION ALL 不会丢数据
SQL Server 中 UNPIVOT 要求所有被转列的数据类型兼容，否则得先用 CAST 统一，比如 CAST(age AS VARCHAR(10))

示例（SQL Server）：

SELECT id, col_name, col_value
FROM (SELECT id, CAST(name AS VARCHAR(50)) AS name, CAST(age AS VARCHAR(10)) AS age FROM users) t
UNPIVOT (col_value FOR col_name IN (name, age)) u;

UNION ALL 手动拼接时最容易漏的三件事

当数据库不支持 UNPIVOT（比如 MySQL 8.0 之前、SQLite），或你需要保留 NULL 值、加额外条件逻辑时，UNION ALL 是唯一选择。但它极易因疏忽导致结果错乱或性能陡增。

每个子查询的列别名必须一致，否则最终结果列名取第一个子句的别名 —— 如果你写 SELECT id, name AS value, 'name' AS field FROM t 和 SELECT id, age AS val, 'age' AS f FROM t，第二段的 val 和 f 会被忽略
没加 WHERE 过滤会导致重复扫描全表多次 —— 5 个列 = 扫 5 遍，大表直接变慢几倍
字符串和数字混用时，MySQL 会静默转成数字再比较，可能让 '123abc' 变成 123，建议统一用 CONVERT(col USING utf8mb4) 或 CAST(col AS CHAR)

示例（通用 SQL）：

SELECT id, 'name' AS field, CAST(name AS CHAR) AS value FROM users
UNION ALL
SELECT id, 'age', CAST(age AS CHAR) FROM users
UNION ALL
SELECT id, 'city', CAST(city AS CHAR) FROM users;

MySQL 8.0+ 怎么绕过没有 UNPIVOT 的限制

MySQL 直到 8.0 都没原生 UNPIVOT，但可以用 JSON_TABLE + JSON_OBJECT 组合模拟，本质是把行转成 JSON，再展开。这招不依赖列数，还能动态处理新增字段，但代价是可读性下降、执行计划更难优化。

必须确保 MySQL 版本 ≥ 8.0.4，且 JSON_TABLE 函数可用（部分云厂商默认禁用）
JSON_OBJECT 会把所有字段转成字符串，数值精度可能丢失（比如 DECIMAL(10,2) 变成带小数点的字符串）
不能直接在视图里嵌套 JSON_TABLE（某些版本报 Invalid use of group function），得用 CTE 或临时表中转

示例（MySQL 8.0.4+）：

SELECT id, jt.field, jt.value
FROM users,
JSON_TABLE(
  JSON_OBJECT('name', name, 'age', age, 'city', city),
  '$' COLUMNS (
    field VARCHAR(20) PATH '$.key',
    value VARCHAR(100) PATH '$.value'
  )
) AS jt;