我有一个名为users
的表,其中有一个名为activated_at
的列,我想通过检查该列是否为空来计算有多少用户被激活。然后像这样并排显示:
+----------+-----------+---------------+-------+
| Malaysia | Activated | Not Activated | Total |
+----------+-----------+---------------+-------+
| Malaysia | 5487 | 303 | 5790 |
+----------+-----------+---------------+-------+
这是我的SQL:
select "Malaysia",
(select count(*) from users where activated_at is not null and locale='en' and date_format(created_at,'%m')=date_format(now(),'%m')) as "Activated",
(select count(*) from users where activated_at is null and locale='en' and date_format(created_at,'%m')=date_format(now(),'%m')) as "Not Activated",
count(*) as "Total"
from users
where locale="en"
and date_format(created_at,'%m')=date_format(now(),'%m');
在我的代码中,我必须三次指定所有where语句,这显然是多余的。我如何重构它?
当做,
马克。
最佳答案
不确定MySql是否支持CASE构造,但我通常通过以下方式处理此类问题:,
select "Malaysia",
SUM(CASE WHEN activated_at is not null THEN 1 ELSE 0 END) as "Activated",
SUM(CASE WHEN activated_at is null THEN 1 ELSE 0 END as "Not Activated",
count(*) as "Total"
from users
where locale="en" and date_format(created_at,'%m')=date_format(now(),'%m');