我试图查询过去X天中每天的多个状态。
我可以通过查询来计算这些值,但是输出并不是我想要的那样,也许我的GROUPBY语句中有错误。所以这就是我现在所拥有的:
示例表:
date - status
02-12-2015 - 1
02-12-2015 - 1
02-12-2015 - 3
02-12-2015 - 2
03-12-2015 - 1
03-12-2015 - 2
我的问题:
SELECT
DATE_FORMAT( STR_TO_DATE( crdate , "%d-%m-%Y %H:%i:%S" ) , "%d-%m-%y" ) as date , COUNT(*) as countone
FROM TBL_NAME
WHERE status='1'
UNION
SELECT
DATE_FORMAT( STR_TO_DATE( crdate , "%d-%m-%Y %H:%i:%S" ) , "%d-%m-%y" ) as date , COUNT(*) as counttwo
FROM TBL_NAME
WHERE status='2'
UNION
SELECT
DATE_FORMAT( STR_TO_DATE( crdate , "%d-%m-%Y %H:%i:%S" ) , "%d-%m-%y" ) as date , COUNT(*) as countthree
FROM TBL_NAME
WHERE status='3'
GROUP BY DATE_FORMAT( STR_TO_DATE( crdate , "%d-%m-%Y %H:%i:%S" ) , "%d-%m-%y" ), status ORDER BY DATE_FORMAT( STR_TO_DATE( crdate , "%d-%m-%Y %H:%i:%S" ) , "%d-%m-%y" ) ASC
假设输出如下:
date - countone - counttwo - countthree
02-12-2015 - 2 - 1 - 1
03-12-2015 - 1 - 1 - 0
问题是我的输出只是按日期分组。
我的输出如下:
date - countone
02-12-2015 - 2
02-12-2015 - 1
02-12-2015 - 1
03-12-2015 - 1
03-12-2015 - 1
有人能帮我实现这个目标吗?我做错什么了?
最佳答案
您可以首先使用GROUPBY计算状态计数,然后旋转表以获得所需的结果。Union函数将始终连接行。
select datefield,
max(if(status=1,count,0)) as countone,
max(if(status=2,count,0)) as counttwo,
max(if(status=3,count,0)) as countthree
from (select datefield, status, count(*) as count
from TBL_NAME
group by datefield,status) A
group by datefield;
检查它的SQL fiddle设置SQL Fiddle
关于mysql - 最近X天中按天计算多个状态,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/34046584/