当在与每个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

10-04 20:18