MySQL - 如何将列转换为行？

Question

ID | a  | b  | c 
1  | a1 | b1 | c1
2  | a2 | b2 | c2

我该如何将行重新组织为ID、columntitle和value？

1 | a1 | a
1 | b1 | b
1 | c1 | c
2 | a2 | a
2 | b2 | b
2 | c2 | c

- user2128539

---

你是指 'ID，value，columntitle' 吗？ - Iswanto San

是的，但顺序并不太重要，只要行是ID - 列标题 - 值就可以。 - user2128539

Answer 1

您正在尝试“反转”数据。MySQL没有“反转”函数，因此您需要使用“UNION ALL”查询将列转换为行：

select id, 'a' col, a value
from yourtable
union all
select id, 'b' col, b value
from yourtable
union all
select id, 'c' col, c value
from yourtable

请参见带演示的SQL Fiddle。

这也可以使用CROSS JOIN完成：

select t.id,
  c.col,
  case c.col
    when 'a' then a
    when 'b' then b
    when 'c' then c
  end as data
from yourtable t
cross join
(
  select 'a' as col
  union all select 'b'
  union all select 'c'
) c

查看 带演示的SQL Fiddle

- Taryn

Answer 2

经过漫长等待，MySQL 8.0.14 版本终于添加了支持 Lateral Join - 官方术语是 lateral derived tables。

这是一个非常强大的功能，在多种情况下非常方便实用，包括将表列转换为行。

您可以按如下方式构造查询：

select t.id, x.*
from mytable t
cross join lateral (
    select a, 'a' 
    union all select b, 'b'
    union all select c, 'c'
) as x(col1, col2)

这看起来与典型的规范解决方案相比并没有太大的区别 - 毕竟，我们仍然在侧向派生表中使用union all... 但不要误解：这个查询只扫描了一次表，而另一种方法需要为每个列进行一次扫描以进行反转。因此，这更有效率 - 随着表格变得更大和/或需要反转的列数增加，性能收益会显著提高。

底线：如果您正在运行MySQL 8.0.14或更高版本，请使用此技术。从该版本开始，这是MYSQL中反转的规范方式。

DB Fiddle演示:

样本数据：

ID | a  | b  | c 
-: | :- | :- | :-
 1 | a1 | b1 | c1
 2 | a2 | b2 | c2

查询结果：

id | col1 | col2 -: | :--- | :--- 1 | a1 | a 1 | b1 | b 1 | c1 | c 2 | a2 | a 2 | b2 | b 2 | c2 | c

---

更新

MySQL 8.0.19首次引入了VALUES语句的支持，这可以通过在子查询中删除使用union all来进一步缩短查询（虽然我在这里没有看到任何性能提升，但这使查询更加整洁）。从版本8.0.30开始，我们可以这样表达lateral join：

select t.id, x.*
from mytable t
cross join lateral (values 
    row(a, 'a'), 
    row(b, 'b'),
    row(c, 'c')
) as x(col1, col2);

- GMB

---

使用带有侧向派生表的值语句时，似乎出现了一个错误。值语句仅输出外部表的第一行数据。 - undefined

Answer 3

尝试使用UNION ALL。

SELECT ID, a, 'a' 
FROM tbl
WHERE ID = 1
UNION
SELECT ID, b, 'b' 
FROM tbl
WHERE ID = 2

- Iswanto San

---

去掉WHERE子句。否则，您只会为每个ID显示一个列，而不是每个ID的所有列。 - Barmar