代码之家 › 专栏 › 技术社区 › khairul

如何重构此SQL查询?

mysql sql

khairul · 技术社区 · 16 年前

我有一张桌子叫 users activated_at

+----------+-----------+---------------+-------+
| Malaysia | Activated | Not Activated | Total |
+----------+-----------+---------------+-------+
| Malaysia |      5487 |           303 |  5790 | 
+----------+-----------+---------------+-------+

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语句,这显然是多余的。我如何重构这个?

当做 MK。

3 回复 | 直到 16 年前

Darrel Miller 16 年前

不确定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');

Nick Hristov 16 年前

SELECT 
    COUNT( CASE WHEN activated_at IS NOT NULL THEN 1 ELSE 0 END) as "Activated",
    COUNT( CASE WHEN activated_at IS NULL THEN 1 ELSE 0 END) as "Not Activated",
    COUNT(*) as "Total"
FROM users WHERE locale="en" AND date_trunc('month', now()) = date_trunc('month' ,created_at);

Lukman 16 年前

我想这会管用的。。但未经测试:

select "Malaysia",
    (select count(*) from users2 where activated_at is not null) as "Activated",
    (select count(*) from users2 where activated_at is null) as "Not Activated",
    count(*) as "Total"
    from (select * from users where locale='en' and  date_format(created_at,'%m')=date_format(now(),'%m')) users2

编辑:那不行。。很抱歉根据其他人的建议使用该案例。。我希望我能删除这个答案。。

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前