是否可以将查询的输出转换为可能?
问题描述:
我从这个查询的输出:是否可以将查询的输出转换为可能?
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 |
+------------+------------------------+-------------------------------+
答
可以使用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;
+0
辉煌!没有支点,没有其他的东西....太好了...非常感谢。 – Sallyerik 2014-11-21 10:30:07
答
您可以使用以下代码 -
select Date, [0], [1]
from
(select Date,Status,persons
from TableA
where Date='2014-11-04') AS SourceTable
PIVOT
(
COUNT(persons)
FOR Status IN ([0],[1])
) AS PivotTable;
[MySQL数据透视表]可能的重复(http://*.com/questions/7674786/mysql-pivot-table) – 2014-11-21 09:12:38