mysql-如何将列解压到行?

ryevplcw  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(317)

我现在可能看不太清楚,但mysql中有一个表,如下所示:

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

出于某种原因(实际上是另一个表上的连接-基于 ID ,但我想如果有人能帮我完成这部分,我可以自己完成剩下的),我需要这些行变成这样:

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

所以基本上,我需要像这样查看行: ID , columntitle , value 有什么方法可以很容易地做到这一点吗?

jhdbpxl9

jhdbpxl91#

尽量使用联合所有。

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

xxls0lw82#

您正在尝试取消打印数据。mysql没有unpivot函数,因此您必须使用 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

请参阅SQLFiddle演示。
也可以使用 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

请参阅SQLFiddle演示

4smxwvx5

4smxwvx53#

这花了很长时间,但mysql版本8.0.14最终增加了对横向联接的支持——官方术语是横向派生表。
这是一个非常强大的特性,在多种情况下都很方便,包括取消表列到行的分隔。
您可以使用以下短语来表达查询:

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)

看起来这和典型的Cannonic解决方案相比并没有太大的区别——毕竟,我们仍在使用 union all 在横向派生表中。。。但是不要搞错了:这个查询只扫描表一次,而另一种方法需要对每一列进行一次扫描才能取消pivot。因此,这样做效率更高,而且随着表变大和/或需要取消填充更多的列,性能增益会显著增加。
一句话:如果您运行的是mysql 8.0.14或更高版本,只需使用这种技术。从那个版本开始,这就是在mysql中取消pivot的标准方法。
db小提琴演示:
样本数据:

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.21版开始,这个功能还不能运行,这可能会被认为是一个bug,但在将来的版本中可能会…:

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

相关问题