mysql - 如何创建额外的列使用列值？

Question

我遇到了以下问题

我有一张这样的 table

+------+---------+------------+
| imei | channel |  sys_date  |
+------+---------+------------+
| 1111 |unknown01| 2013-04-15 |
| 2222 |unknown02| 2013-04-15 |
| 3333 |unknown03| 2013-04-15 |
| 4444 |unknown01| 2013-04-16 |
| 5555 |unknown02| 2013-04-17 |
| 6666 |unknown02| 2013-04-17 |
+------+---------+------------+

现在我需要写一个像这样的 SQL let 结果(让 channel 的值作为额外的列)

+----------+---------+---------+---------+
| sys_date |unknown01|unknown02|unknown03|
+----------+---------+---------+---------+
|2013-04-15|    1    |    1    |    1    |
|2013-04-16|    1    |    0    |    0    |
|2013-04-17|    0    |    2    |    0    |
+----------+---------+---------+---------+

我不知道这个sql，所以我需要帮助，谢谢

Answer 1

你可以为此使用CASE

SELECT sys_date
,SUM(CASE channel WHEN 'unknown01' THEN 1 ELSE 0 END) AS unknown01
,SUM(CASE channel WHEN 'unknown02' THEN 1 ELSE 0 END) AS unknown02
,SUM(CASE channel WHEN 'unknown03' THEN 1 ELSE 0 END) AS unknown03
FROM Table1
GROUP BY sys_date

See this SQLFiddle

如果您不知道可以使用此动态查询的 channel 数量:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when channel = ''',
      channel,
      ''' then 1 else 0 end) AS ',
      channel
    )
  ) INTO @sql
FROM Table1;


SET @sql = CONCAT('SELECT sys_date, ', @sql, '
                  FROM Table1 
                  GROUP BY sys_date');

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

See this SQLFiddle