MySQL查询start_time与end_time相同且满足关联条件的所有行id

实现逻辑 #

1. 先关联order_status_history表过滤出所有符合状态要求的订单：要求关联的状态记录is_current = true且status_keyword在指定枚举范围内，不满足的订单直接排除。
2. 对过滤后的有效订单按start_time、end_time分组，筛选出分组行数≥3的时间对。
3. 将有效订单表和符合要求的分组关联，取出对应分组下的所有订单id即可。

该逻辑天然满足「分组内任意行不满足关联条件则整个分组不返回」的要求：只要分组内有一条订单不符合状态规则，就会被提前过滤，最终分组计数≥3的分组下所有订单全部符合状态要求。

可直接使用的SQL语句（支持CTE的数据库，如MySQL 8.0+、PostgreSQL等） #

WITH valid_orders AS (
    SELECT o.id, o.start_time, o.end_time
    FROM orders o
    INNER JOIN order_status_history osh 
        ON o.id = osh.order_id 
        AND osh.is_current = TRUE
        AND osh.status_keyword IN ('pending', 'accepted', 'on_the_way', 'assigned', 'reached', 'started')
),
qualified_groups AS (
    SELECT start_time, end_time
    FROM valid_orders
    GROUP BY start_time, end_time
    HAVING COUNT(*) >= 3
)
SELECT vo.id
FROM valid_orders vo
INNER JOIN qualified_groups qg 
    ON vo.start_time = qg.start_time 
    AND vo.end_time = qg.end_time;

兼容低版本数据库的子查询写法（如MySQL 5.x） #

SELECT vo.id
FROM (
    SELECT o.id, o.start_time, o.end_time
    FROM orders o
    INNER JOIN order_status_history osh 
        ON o.id = osh.order_id 
        AND osh.is_current = TRUE
        AND osh.status_keyword IN ('pending', 'accepted', 'on_the_way', 'assigned', 'reached', 'started')
) vo
INNER JOIN (
    SELECT start_time, end_time
    FROM (
        SELECT o.start_time, o.end_time
        FROM orders o
        INNER JOIN order_status_history osh 
            ON o.id = osh.order_id 
            AND osh.is_current = TRUE
            AND osh.status_keyword IN ('pending', 'accepted', 'on_the_way', 'assigned', 'reached', 'started')
    ) t
    GROUP BY start_time, end_time
    HAVING COUNT(*) >= 3
) qg ON vo.start_time = qg.start_time AND vo.end_time = qg.end_time;

注意事项 #

如果单个订单在order_status_history表中存在多条is_current = true的记录，需要在计数和取id时添加DISTINCT去重，避免重复统计，例如将COUNT(*)改为COUNT(DISTINCT o.id)，取id时改为SELECT DISTINCT o.id。

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