MySQL同时显示数据透视表和直方图

Question

我目前有一个以下形式的表格

account_id | phone_name
0          | samsung M360
12         | HTC One
58         | LG Optimus
12         | Novatel
.            .
.            .
.            .

我想将其转换为以下形式的表格

account_id | samsung M360 | HTC One | LG Optimus | Novatel | ...
0            1              0         0            0
12           0              1         0            1
58           0              0         1            0
.
.
.

即我想制作一个数据透视表，其中包含每个account_id 的phone_names 计数。主要问题是有 50 多个不同的电话名称，因此我无法通过列出每个 phone_name 以通常的方式创建 MySQL 数据透视表。这可能吗？

Answer 1

MySQL 没有 PIVOT 函数，但您可以使用带有 CASE 表达式的聚合函数将行转换为列。

如果您的数量或 phone_name 值有限，则可以对查询进行硬编码:

select account_id,
  sum(case when phone_name = 'samsung M360' then 1 else 0 end) SamsungM360,
  sum(case when phone_name = 'HTC One' then 1 else 0 end) HTCOne,
  sum(case when phone_name = 'LG Optimus' then 1 else 0 end) LGOptimus,
  sum(case when phone_name = 'Novatel' then 1 else 0 end) Novatel
from yt
group by account_id;

参见SQL Fiddle with Demo .

但是如果这些值未知，那么您将需要使用准备好的语句来生成动态 SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN phone_name = ''',
      phone_name,
      ''' THEN 1 else 0 END) AS `',
      phone_name, '`'
    )
  ) INTO @sql
FROM yt;

SET @sql 
  = CONCAT('SELECT account_id, ', @sql, ' 
            from yt
            group by account_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见SQL Fiddle with Demo 。两个查询都给出结果:

| ACCOUNT_ID | SAMSUNG M360 | HTC ONE | LG OPTIMUS | NOVATEL |
--------------------------------------------------------------
|          0 |            1 |       0 |          0 |       0 |
|         12 |            0 |       1 |          0 |       1 |
|         58 |            0 |       0 |          1 |       0 |

注意:GROUP_CONCAT()生成的字符串的长度默认值为 1024。因此，您可能需要更改 group_concat_max_length 上的 session 设置。如果你的字符串太长。