MYSQL 选择/分组依据 : How do I remove a group from the result based on a value of one of it's members?

Question

考虑下面的事件表。 OrderID 字段是 Orders 表的外键。

OrderID     EventType     TimeAdded*       Processed   1           ...        3 Hours Ago          0   2           ...        5 Minutes Ago        0   1           ...        2 Hours Ago          0   1           ...        1 Hour Ago           0       3           ...        12 Minutes ago       1   3           ...        3 Hours Ago          0   2           ...        2 Hours Ago          0

*TimeAdded is actual a mysql date-time field. I used human readable times here to make the question easier to read.

I need to get a result set that has each OrderID, only once, but only if ALL records attached to a given order ID, that have a proceeded status of 0, have been added more than 15 minutes ago.

In this example, order #2 should be OMITTED from the result set because it has an unprocessed event added within the last 15 minutes. The fact that it has an unprocessed event from 2 hours ago is inconsequential.

Order #3 SHOULD be included. Even though one of its entries was added 12 minutes ago, that entry has already been processed, and should not be considered. Only the "3 hours ago" entry should be considered in this set, and it is greater than 15 minutes ago.

Order #1 SHOULD be included, since all three unprocessed events attached to it occurred more than 15 minutes ago.

I'm not sure if this needs to use a sub-query or group by or possibly both?

pseudo code:

SELECT * FROM OrderEvents WHERE Processed=0 GROUP BY OrderID
    OMIT WHOLE GROUP IF GROUP_MIN(TimeAdded) >= (NOW() - INTERVAL 15 MINUTE)

我只是不确定语法是否正确？

Answer 1

这将完成工作(在 MySQL Workbench 中测试):

SELECT * FROM OrderEvents WHERE Processed = 0 GROUP BY OrderID
    HAVING MAX(TimeAdded) < (NOW() - INTERVAL 15 MINUTE);