mysql - SQL QUERY-在 GROUP BY 中使用时，多个 COUNT 返回错误(相同)的结果

Question

我会简化这个:

我有两个工作正常的 SQL 表达式:

首先:

select count(*) as number1
 from T1
where DATE1>'2012-01-01' and DATE2<'2012-12-31'

结果:1​​3

select  count(*) as number2 
from T1
where DATE3>DATE2 and CURDATE()>DATE2

结果:5

但是当我尝试在 GROUP BY 中插入这两个计数时，我得到的结果总是 13!!!

SELECT NAME,
 COUNT(case when DATE1>'2012-01-01' and DATE2<'2012-12-31' then 1 else 0 end) as number1, 
COUNT (case when (DATE3>DATE2 and CURDATE()>DATE2) then 1 else 0 end) as number 2
from T1

我得到的结果是:

NAME  NUMBER1  NUMBER2
A       5         5
B       4         4
C       4         4

但我应该得到:

NAME  NUMBER1  NUMBER2
A       5         4
B       4         0
C       4         1

因此列的总和为 13 和 5，就像在前两个查询中一样。我究竟做错了什么？谢谢

Answer 1

COUNT(expression) 计算非空表达式。您可以通过将 ELSE 0 更改为 ELSE NULL 或删除它来修改查询(ELSE NULL 隐含在 CASE 表达式):

SELECT name,
    COUNT(CASE WHEN date1 > '2012-01-01' AND date2 < '2012-12-31' 
              THEN 1 END
         ) AS number1, 
    COUNT(CASE WHEN date3 > date2 AND CURDATE() > date2 
              THEN 1 END
         ) AS number2
FROM T1 
GROUP BY name ;