sql - 正确使用 HAVING 与 group by 和 COUNT

Question

我正在运行此查询:

SELECT u.id as id, 
       COUNT(DISTINCT YEAR(TIMESTAMP), WEEK(TIMESTAMP)) cc, 
       GROUP_CONCAT(DISTINCT YEAR(TIMESTAMP),'-',WEEK(TIMESTAMP)) a
FROM   users u 
       JOIN checkins c 
         ON c.userid = u.id 
GROUP  BY userid
HAVING COUNT(cc) = 3

这会产生以下结果:

id  cc  a
05  3   2010-43,2010-47,2010-45 
06  2   2010-44,2010-45 
13  3   2010-43,2010-45,2010-48 
20  3   2010-45,2010-43,2010-47 
21  3   2010-43,2010-47,2010-45 
22  2   2010-47,2010-48 
25  3   2010-48,2010-43,2010-46 
27  2   2010-42,2010-47 
30  2   2010-48,2010-45 
41  3   2010-44,2010-45,2010-47 
44  2   2010-42,2010-44 
50  2   2010-44,2010-47 
52  2   2010-48,2010-47 
57  2   2010-43,2010-44 
71  3   2010-43,2010-48,2010-47 
72  2   2010-43,2010-44 
78  3   2010-47,2010-42,2010-43 
79  2   2010-45,2010-46 
80  2   2010-46,2010-44 
87  1   2010-46 
97  1   2010-48 
108 3   2010-43,2010-47,2010-45 

如您所见，cc 列的值为 2、3 甚至 1。

当我用 HAVING 告诉它应该是 3 时，这是怎么回事？

Answer 1

MySQL 允许在having 子句中使用别名。您需要使用:

HAVING cc = 3

不是

HAVING COUNT(cc) = 3

为了过滤结果以仅包含 cc 值为 3 的行。我实际上很不确定为什么 HAVING COUNT(cc) = 3 会返回任何结果。