使用 GROUP_CONCAT() 的 Mysql 查询

Question

使用以下 mysql 查询:

SELECT DATE_FORMAT(time, '%Y-%m-%d') AS time,report,count(ip) AS countip FROM asn_data WHERE DATE_FORMAT(time, '%Y-%m-%d') > '2012-10-31' GROUP BY DATE_FORMAT(time,'%Y-%m-%d'),report;

我得到以下输出:

+------------+---------------+---------+
| time       | report        | countip |
+------------+---------------+---------+
| 2012-11-01 | bots          |      76 |
| 2012-11-01 | openresolvers |      97 |
| 2012-11-01 | proxy         |      24 |
| 2012-11-01 | scanners      |       4 |
| 2012-11-01 | spam          |     881 |
| 2012-11-02 | bots          |     142 |
| 2012-11-02 | proxy         |      22 |
| 2012-11-02 | spam          |     667 |
| 2012-11-03 | proxy         |       1 |
+------------+---------------+---------+

获得以下输出的查询是什么:

+------------+-------------+---------------+-------+----------+------+
| time       | bots        | openresolvers | Proxy | scanners | spam |
+------------+-------------+---------------+-------+----------+------+
| 2012-11-01 | 76          | 97            | 24    | 4        | 881  |
| 2012-11-02 | 142         | 0             | 22    | 0        | 667  |
| 2012-11-03 | 0           | 0             | 1     | 0        | 0    |
+------------+-------------+---------------+-------+----------+------+

Answer 1

这里不需要组连接，你需要旋转结果集，尝试这种方式:

SELECT DATE_FORMAT(time, '%Y-%m-%d') AS time,
SUM(CASE WHEN report = 'bots' THEN ipc ELSE 0 END) AS bots,
SUM(CASE WHEN report = 'openresolvers' THEN ipc ELSE 0 END) AS openresolvers,
SUM(CASE WHEN report = 'Proxy' THEN ipc ELSE 0 END) AS Proxy,
SUM(CASE WHEN report = 'scanners' THEN ipc ELSE 0 END) AS scanners,
SUM(CASE WHEN report = 'spam' THEN ipc ELSE 0 END) AS spam
FROM (
SELECT count(ip) AS ipc, report, DATE(time) as time 
FROM  asn_data 
GROUP BY report, DATE(time)) i
WHERE DATE_FORMAT(time, '%Y-%m-%d') > '2012-10-31'
GROUP BY time;