当在与每个uhid和按月分组的response_key 95到98之间找到response_val = 4时,返回一个计数
|response_key | response_val | month | uhid |
|---------------------------------------------|
| 95 | 4 | 2019-09| 14569|
| 96 | 4 | 2019-09| 14569|
| 97 | 5 | 2019-09| 14569|
| 98 | 5 | 2019-09| 14569|
| 95 | 5 | 2019-09| 13256|
| 96 | 5 | 2019-09| 13256|
| 97 | 5 | 2019-09| 13256|
| 98 | 5 | 2019-09| 13256|
| 95 | 5 | 2019-09| 25689|
| 96 | 5 | 2019-09| 25689|
| 97 | 5 | 2019-09| 25689|
| 98 | 5 | 2019-09| 25689|
|---------------------------------------------|
我试过SQL查询为
SELECT month,
COUNT(CASE
WHEN `response_key` IN (95,96,97,98) and `response_val`='5'
THEN 1 ELSE NULL END) AS 'yes',
COUNT(CASE
WHEN `response_key` IN (81,82,83,84,85,86,87) and `response_val`='4'
THEN 1 ELSE NULL END) AS 'no'
FROM audit WHERE group by month
但当所有response_key的值为4时,它返回计数。
我期望结果
| count | month |
-----------------
| 1 |2019-09|
最佳答案
也许以下将为您提供所需的东西:
SELECT `month`,
COUNT(CASE response_val WHEN 4 THEN 1 END) AS `yes`,
COUNT(CASE response_val WHEN 5 THEN 1 END) AS `no`
FROM audit
WHERE response_key between 95 AND 98
GROUP BY `month`
我简化了case语句,并将
response_key
条件移到where
子句中(仅当要返回ELSE
以外的值时,CASE
语句中的null
部分才是必需的)。您可以在此处找到有效的演示:https://rextester.com/PCN24609
该查询的结果计数为:
month yes no
2019-09 1 11
好了,您可以根据需要交换输出列的顺序,如果您只对
response_val=4
计数感兴趣,请按照以下步骤操作:SELECT COUNT(CASE response_val WHEN 4 THEN 1 END) AS `count`,
`month`
FROM audit
WHERE response_key between 95 AND 98
GROUP BY `month`
演示:https://rextester.com/JEV40175
结果:
| count | month |
-------------------
| 1 | 2019-09 |
编辑:
“当在response_key 95之间找到response_val = 4时,返回一个计数
到98,分别对应每个uhid和按月分组”
这意味着您也可以对
uhid
进行分组,如下所示:SELECT COUNT(CASE response_val WHEN 4 THEN 1 END) AS `count`,
`month`,uhid
FROM audit
WHERE response_key between 95 AND 98
GROUP BY `month`,uhid;
-- results:
count month uhid
0 2019-09 13256
2 2019-09 14569
1 2019-09 25689
或者,如果您希望每月在同一行中记录所有
uhid
计数:SELECT `month`,
COUNT(CASE uhid WHEN 14569 THEN 1 END) AS `cnt_14569`,
COUNT(CASE uhid WHEN 13256 THEN 1 END) AS `cnt_13256`,
COUNT(CASE uhid WHEN 25689 THEN 1 END) AS `cnt_25689`
FROM audit
WHERE response_key between 95 AND 98 AND response_val=4
GROUP BY `month`,uhid
-- results:
month cnt_14569 cnt_13256 cnt_25689
2019-09 2 0 0
2019-09 0 0 1
演示:https://rextester.com/WXQ73318
2.编辑:按照您的最新评论,以下内容可能会满足您的要求
(“列出每月每个
response_val=4
的独立用户数”):SELECT COUNT(`count`) usrcnt, `month` FROM (
SELECT COUNT(CASE response_val WHEN 4 THEN 1 END) AS `count`,`month`,uhid
FROM audit WHERE response_key between 95 AND 98
GROUP BY `month`,uhid
) tbl WHERE `count`>0 GROUP BY `month`;
-- or, even shorter:
SELECT COUNT(DISTINCT CASE response_val WHEN 4 THEN uhid END) AS `count`,`month`
FROM audit WHERE response_key between 95 AND 98
GROUP BY `month`;
-- same result from both queries:
usrcnt month
2 2019-09
演示:https://rextester.com/UGN73913
最新查询基于以下表值:
response_key response_val month uhid
95 4 2019-09 14569
96 4 2019-09 14569
97 5 2019-09 14569
98 5 2019-09 14569
95 5 2019-09 13256
96 5 2019-09 13256
97 5 2019-09 13256
98 5 2019-09 13256
95 5 2019-09 25689
96 4 2019-09 25689
97 5 2019-09 25689
98 5 2019-09 25689