MySql 将多行转成列——大数据最优化的方式? [英] MySql transpose multiple rows into column - the most optimized way for large data?

问题描述

我想将多行转置为列.

这是我的数据表(大约 20 百万行)

This my table with data (about 20 mil.rows)

PHONE      SERVICE
0000000    service1
0000000    service2
0000000    service3
1111111    service1
1111111    service4
2222222    service5

我想得到以下输出:

PHONE      SC1       SC2       SC3       SC4   SC5
0000000    service1  service2  service3  NULL  NULL
1111111    service1  service4  NULL      NULL  NULL
2222222    service5  NULL      NULL      NULL  NULL

等等.

有人知道这样做的最快速度吗(大约 2000 万条记录)?非常感谢！

Anybody know the fastest to do this (for about 20mil records)? Thanks very much!

推荐答案

这应该很有效，请确保您在 phone 上有索引.

This should work well, make sure you have an index on phone.

SELECT phone,
       SUBSTRING_INDEX(services, ',', 1) SC1,
       if(service_count >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 2), ',', -1), NULL) SC2,
       if(service_count >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 3), ',', -1), NULL) SC3,
       if(service_count >= 4, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 4), ',', -1), NULL) SC4,
       if(service_count >= 5, SUBSTRING_INDEX(SUBSTRING_INDEX(services, ',', 5), ',', -1), NULL) SC5
FROM (SELECT phone, GROUP_CONCAT(service) AS services, COUNT(*) as service_count
      FROM phones
      GROUP BY phone) AS x

演示

这篇关于MySql 将多行转成列——大数据最优化的方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！