MySQL - 试图将 3 个 SELECTS 合并为一个

Question

我需要运行以下 3 个选择查询:

SELECT profile_gender AS Gender, COUNT(*) AS Users FROM login GROUP BY profile_gender;

SELECT profile_gender AS Gender, COUNT(*) AS Users FROM login WHERE profile_createdate > DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY profile_gender;

SELECT profile_gender AS Gender, COUNT(*) AS Users FROM login WHERE profile_createdate > DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY profile_gender;

profile_gender 为 0 或 1，并且位于同一列中。是否可以在一次查询中获取此信息？这有点超出了我目前的查询能力。

谢谢

Answer 1

在实际字段列表中进行计数:

SELECT profile_gender AS Gender,
    SUM(profile_createdate > (CURDATE() - INTERVAL 1 DAY)) AS Day1,
    SUM(profile_createdate > (CURDATE() - INTERVAL 7 DAY)) AS Day7,
    COUNT(profile_createdate) AS Total
FROM login
GROUP BY profile_gender

比较的 bool 结果将被 mysql 类型转换为普通整数 0 或 1，然后可以是 SUM()编辑。