mysql - 是否可以对查询的输出进行透视？

Question

我有这个查询的输出:

select Date,Status, count(distinct persons)from TableA where Date='2014-11-04' group by Status;

+------------+------------------------+-------------------------------+
| Date       | Status                 | count(distinct persons)       |
+------------+------------------------+-------------------------------+
| 2014-11-04 | 0                      |                            45 |
| 2014-11-04 | 1                      |                            93 |
+------------+------------------------+-------------------------------+

我想得到的是:

+------------+------------------------+-------------------------------+
| Date       | 0                      |     1                         |
+------------+------------------------+-------------------------------+
| 2014-11-04 | 45                     |    93                         |
+------------+------------------------+-------------------------------+

Answer 1

您可以使用 CASE 在 COUNT 函数中放置一个条件:

SELECT  Date,
        COUNT(DISTINCT CASE WHEN status = 0 THEN persons END) AS `0`,
        COUNT(DISTINCT CASE WHEN status = 1 THEN persons END) AS `1`
FROM    TableA
WHERE   Date = '2014-11-04'
GROUP BY Date;