如何让MySQL子查询复用主查询的WHERE时间条件？

当然可以做到！重复写相同的时间条件不仅麻烦，还容易在修改时漏改导致数据错误。下面给你几个实用的方案，既能复用主查询的时间范围，又能保证统计准确：

方案1：用CTE（公共表表达式）定义时间范围（推荐，MySQL 8.0+支持） #

CTE可以帮你把时间范围只定义一次，后续主查询和子查询都能直接引用它，非常直观：

WITH time_range AS (
    SELECT '2018-03-10 00:00:00' AS start_time, '2018-03-10 13:25:00' AS end_time
)
SELECT
    "Total" as "Total",
    sum(case when s.stage = 'Main Stage' then 1 else 0 end) AS 'Main Stage',
    sum(case when s.stage = 'Secondary Stage' then 1 else 0 end) AS 'Secondary Stage',
    (
        SELECT count(distinct t.ticket_number) 
        FROM events e
        JOIN stages s ON e.stage_id = s.id  -- 注意：原查询里的`o.id`应该是笔误，改成`s.id`才符合关联逻辑
        JOIN tickets t ON t.id = e.ticket_id
        CROSS JOIN time_range tr
        WHERE s.stage = 'Third Stage' 
          AND e.started > tr.start_time 
          AND e.started < tr.end_time
    ) - 
    (
        SELECT count(distinct t.ticket_number) 
        FROM events e
        JOIN stages s ON e.stage_id = s.id
        JOIN tickets t ON t.id = e.ticket_id
        CROSS JOIN time_range tr
        WHERE s.stage = 'Fourth Stage'
          AND e.started > tr.start_time 
          AND e.started < tr.end_time
    ) as 'Other Stages'
FROM events e
JOIN stages s ON e.stage_id = s.id
JOIN tickets t ON t.id = e.ticket_id
CROSS JOIN time_range tr
WHERE e.started > tr.start_time 
  AND e.started < tr.end_time;

方案2：用派生表兼容旧版本MySQL（5.7及以下） #

如果你的MySQL版本不支持CTE，用派生表也能达到同样的效果，只是写法稍微繁琐一点：

SELECT
    "Total" as "Total",
    sum(case when s.stage = 'Main Stage' then 1 else 0 end) AS 'Main Stage',
    sum(case when s.stage = 'Secondary Stage' then 1 else 0 end) AS 'Secondary Stage',
    (
        SELECT count(distinct t.ticket_number) 
        FROM events e
        JOIN stages s ON e.stage_id = s.id
        JOIN tickets t ON t.id = e.ticket_id
        JOIN (SELECT '2018-03-10 00:00:00' AS start_time, '2018-03-10 13:25:00' AS end_time) tr
        WHERE s.stage = 'Third Stage' 
          AND e.started > tr.start_time 
          AND e.started < tr.end_time
    ) - 
    (
        SELECT count(distinct t.ticket_number) 
        FROM events e
        JOIN stages s ON e.stage_id = s.id
        JOIN tickets t ON t.id = e.ticket_id
        JOIN (SELECT '2018-03-10 00:00:00' AS start_time, '2018-03-10 13:25:00' AS end_time) tr
        WHERE s.stage = 'Fourth Stage'
          AND e.started > tr.start_time 
          AND e.started < tr.end_time
    ) as 'Other Stages'
FROM events e
JOIN stages s ON e.stage_id = s.id
JOIN tickets t ON t.id = e.ticket_id
JOIN (SELECT '2018-03-10 00:00:00' AS start_time, '2018-03-10 13:25:00' AS end_time) tr
WHERE e.started > tr.start_time 
  AND e.started < tr.end_time;

额外优化：合并子查询提升性能 #

原查询里的两个子查询可以合并成一个，减少一次表扫描，效率更高：

WITH time_range AS (
    SELECT '2018-03-10 00:00:00' AS start_time, '2018-03-10 13:25:00' AS end_time
)
SELECT
    "Total" as "Total",
    sum(case when s.stage = 'Main Stage' then 1 else 0 end) AS 'Main Stage',
    sum(case when s.stage = 'Secondary Stage' then 1 else 0 end) AS 'Secondary Stage',
    (
        SELECT 
            SUM(CASE WHEN s.stage = 'Third Stage' THEN 1 ELSE -1 END)
        FROM (
            SELECT DISTINCT t.ticket_number, s.stage
            FROM events e
            JOIN stages s ON e.stage_id = s.id
            JOIN tickets t ON t.id = e.ticket_id
            CROSS JOIN time_range tr
            WHERE s.stage IN ('Third Stage', 'Fourth Stage')
              AND e.started > tr.start_time 
              AND e.started < tr.end_time
        ) AS stage_tickets
    ) as 'Other Stages'
FROM events e
JOIN stages s ON e.stage_id = s.id
JOIN tickets t ON t.id = e.ticket_id
CROSS JOIN time_range tr
WHERE e.started > tr.start_time 
  AND e.started < tr.end_time;

关键说明： #

• 原查询里的join stages s on e.stage_id = o.id应该是笔误，我改成了s.id，否则关联逻辑不成立，你可以根据实际表结构调整。
• 不管用哪种方案，时间范围只需要修改一处，后续所有查询都会自动复用这个条件，完美解决你说的框架限制问题。

内容的提问来源于stack exchange，提问作者user