How to get orders that have multiple rows with different statuses?-CodePudding

I have the following table:

order_id | status | created_at | updated_at
100        1        2022-01-01   2022-01-01
100        2        2022-01-01   2022-01-01
100        3        2022-01-01   2022-01-01
101        1        2022-01-01   2022-01-01
101        2        2022-01-01   2022-01-01
101        3        2022-01-01   2022-01-01
101        4        2022-01-01   2022-01-01
102        1        2022-01-01   2022-01-01
102        2        2022-01-01   2022-01-01

What I want to know is how to obtain orders that have status 3, but do not have status 4 in their history. That is, based on the example I have in the table, I should only be able to get the order with ID of 100.

Right now I manage to get that result with the following query.

SELECT * FROM orders
WHERE created_at BETWEEN '2022-01-01' AND '2022-01-30 23:11:59' AND status = 3 AND order_id NOT IN (
    SELECT order_id FROM orders WHERE status IN(4) GROUP BY order_id
);

However, that long-term query will have performance issues because the subquery will search through all the records. Does anyone know a way to do it better?

CodePudding user response：

Aggregation provides on option:

SELECT order_id
FROM orders
GROUP BY order_id
HAVING SUM(status = 3) > 0 AND SUM(status = 4) = 0;

CodePudding user response：

select o3.*
from orders o3
left join orders o4 on o4.order_id=o3.order_id and o4.status=4
where o3.created_at between '2022-01-01' and '2022-01-30 23:11:59' and o3.status=3 and o4.order_id is null

This excludes orders that have any orders row with status 4; if you only want to exclude ones where created_at is in that range, add an o4.created_at between ... condition to the left join on clause.