mysql - 如何将行转置为列

我有一张桌子，其价值如下所示。稍后将在表的“服务”列中添加“ fbb”类型的值。

SITE_NAME  SERVICE  BANDWIDTH
MBCGP1     3g       30
BONWRT     3g       40
BORNR1     3g       30
MBCGP1     2g       8
BONWRT     2g       4
BORNR1     2g       8
BOSBB1     2g       8

我想将此值插入这样的表中。

SITE_NAME  2g_bw  3g_bw  fbb_bw
MBCGP1     8       30     0
BONWRT     4       40     0
BORNR1     8       30     0
BOSBB1     8       0      0

我不知道如何编写一个sql。

最佳答案

您可以将表的一个实例用于2g，将另一个实例用于3g，然后联接这些表。

select 2g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 2g
join yourtable 3g
  on 3g.site_name = 2g.site_name
 and 3g.service = '3g'
where 2g.service = '2g'

然后，您可以使用INSERT ... SELECT将结果插入另一个表中

insert into othertable (site_name, 2g_bw, 3g_bw, fbb_bw)
select 2g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 2g
join yourtable 3g
  on 3g.site_name = 2g.site_name
 and 3g.service = '3g'
where 2g.service = '2g'

更新资料

按照@Strawberry的建议，您可以使用完全连接从2g或3g（不是两者）中获取空值。

select 2g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 2g
left outer join yourtable 3g
   on 3g.site_name = 2g.site_name
  and 3g.service = '3g'
where 2g.service = '2g'
union
select 3g.site_name, 2g.bandwidth 2g_bw, 3g.bandwidth 3g_bw, 0 fbb_bw
from yourtable 3g
left outer join yourtable 2g
   on 2g.site_name = 3g.site_name
  and 2g.service = '2g'
where 3g.service = '3g'

第一个请求获得与2G和3G相关联的结果，第二个请求与3G和2G相关联的结果。如果您合并该结果，请确保打开所有结果。