mysql - 将两个相似的查询组合在一起

Question

我有 MySQL 查询，这两个查询都可以独立工作，我想将它们组合在一起，以便返回三个值。

查询 1 检查删除了多少帐户:

SELECT
    COUNT(1) AS deleted_count,
    SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM 
    exit_reasons e
WHERE 
    e.timestamp>='$sixmonths'
GROUP BY 
    WEEKOFYEAR(e.timestamp)
ORDER BY
    display_date ASC
LIMIT 26

这会返回一个日期和那一周删除的人数

查询 2 检查其中有多少人随后再次注册:

SELECT
    COUNT(1) AS date_count,
    SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM 
    exit_reasons e
LEFT JOIN
    companies c on e.email=c.email
WHERE
    e.timestamp>='$sixmonths' AND c.email IS NOT NULL
GROUP BY
    WEEKOFYEAR(e.timestamp)
ORDER BY 
    display_date ASC
LIMIT 26

这会返回一个日期和现在拥有新帐户的删除周数

我希望它返回一个日期，然后在一个查询中删除号码并重新加入号码，所以我尝试了:

SELECT
    COUNT(1) AS date_count,
    SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date,
    date_count as rejoined_count from
        (SELECT
            COUNT(1) AS date_count,
            SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
        FROM 
            exit_reasons e2
        LEFT JOIN 
            companies c on e.email=c.email
        LEFT JOIN
            companies_users cu on e.email=cu.email
        WHERE 
            e2.timestamp>='$sixmonths' AND c.email IS NOT NULL
        GROUP BY 
            WEEKOFYEAR(e.timestamp)
        ORDER BY
            display_date ASC
        LIMIT 26)
FROM 
    exit_reasons e
WHERE 
    e.timestamp>='$sixmonths'
GROUP BY 
    WEEKOFYEAR(e.timestamp)
ORDER BY
    display_date ASC
LIMIT 26

但我遇到语法错误 - 如何将这些查询合并为一个查询？

Answer 1

您应该能够通过使用聚合函数以及一些条件逻辑(如 CASE 表达式)将两个查询合并为一个查询:

SELECT
   COUNT(1) AS deleted_count,
   SUM(CASE WHEN c.email IS NOT NULL THEN 1 ELSE 0 END) as date_count,
   SUBDATE(e.timestamp, INTERVAL WEEKDAY(e.timestamp) DAY) AS display_date
FROM exit_reasons e
LEFT JOIN companies c 
    on e.email=c.email
WHERE e.timestamp>='$sixmonths'
GROUP BY WEEKOFYEAR(e.timestamp)
ORDER BY display_date ASC
LIMIT 26;

See Demo .如果 c.email IS NOT NULL 被移动到 SUM(CASE.. 中，您对第二个查询的检查允许您获得非空行的总数空。