如何过滤一对多的所有结果

Question

想象一下下面的表格:

CREATE TABLE item (code string);
CREATE TABLE item_event (id int, item_code string, event int, date smalldatetime);
item:
code
item1
item2
item3
item4
item_event:
id | item_code  | event | date
1  |  item1     | 123   | 2021-05-04 05:50
2  |  item1     | 456   | 2021-05-04 06:50
3  |  item2     | 123   | 2021-05-04 05:50
4  |  item2     | 678   | 2021-05-04 08:50
5  |  item3     | 456   | 2021-05-04 05:50
6  |  item3     | 890   | 2021-05-04 09:50
7  |  item4     | 123   | 2021-05-04 11:50
8  |  item4     | 456   | 2021-05-04 20:50
9  |  item4     | 890   | 2021-05-04 01:50

如何选择没有456事件的项目或日期在890事件之后的456事件(如果有890事件)?

在这种情况下，它将返回item2和item4。

不知道如何在SQL中做到这一点。我所尝试的一切都只是过滤掉单个事件，而不是查看按item_code.">

分组的集合。

Answer 1

使用聚合我们可以尝试:

WITH cte AS (
SELECT item_code
FROM item_event
GROUP BY item_code
HAVING COUNT(CASE WHEN event = 456 THEN 1 END) = 0 OR
MIN(CASE WHEN event = 890 THEN date END) <
MIN(CASE WHEN event = 456 THEN date END)
)
SELECT *
FROM item_event
WHERE item_code IN (SELECT item_code FROM cte);

演示上述HAVING子句的逻辑是保留任何没有456事件的item_code记录集，或任何item_code，其最早的890事件发生在最早的456事件之前。在这种情况下，这意味着每个456事件至少有一个更早的890事件。

Answer 2

有两种方法可以做到这一点，尽管这取决于您要回答的问题。第一个是NOT EXISTS，这可能更适合您的第一个场景(没有456事件)。它看起来像这样:

SELECT Code
FROM dbo.Item I
WHERE NOT EXISTS (SELECT 1
FROM dbo.Item_Event IE
WHERE IE.Item_Code = I.Code
AND IE.Event = 456);

对我们来说，后者将是一个具有条件聚合的HAVING，这可能更适合您的第二个要求，因为需要比较两个不同的事件:

SELECT I.Code
FROM dbo.Item
JOIN dbo.Item_Event IE ON I.Code = IE.Item_Code --This *might* need to be a LEFT JOIN
GROUP BY I.Code
HAVING COUNT(CASE WHEN Event = 456 THEN 1 END) = 0
OR MIN(CASE WHEN Event = 890 THEN date END) < MIN(CASE WHEN Event = 456 THEN Date END);

Answer 3

这看起来像是一个典型的'嵌套否定'练习。当您按照以下方式重新表述时，问题在SQL中变得更容易表达:

• 把没有的每一项都给我
  • 456事件，不伴随
    • 一个日期在456之前的890事件

每个缩进都成为一个子查询。否定由NOT IN或NOT EXISTS表达。

DROP TABLE item IF EXISTS 
DROP TABLE item_event IF EXISTS 
CREATE TABLE item (code string)
CREATE TABLE item_event (id int, item_code string, event int, date smalldatetime)
INSERT INTO item VALUES ('item1'), ('item2'), ('item3'), ('item4')
INSERT INTO item_event VALUES
(1, 'item1', 123, '2021-05-04 05:50'),
(2, 'item1', 456, '2021-05-04 06:50'),
(3, 'item2', 123, '2021-05-04 05:50'),
(4, 'item2', 678, '2021-05-04 08:50'),
(5, 'item3', 456, '2021-05-04 05:50'),
(6, 'item3', 890, '2021-05-04 09:50'),
(7, 'item4', 123, '2021-05-04 11:50'),
(8, 'item4', 456, '2021-05-04 20:50'),
(9, 'item4', 890, '2021-05-04 01:50')
SELECT *
FROM item
WHERE code NOT IN (
SELECT ie456.item_code
FROM item_events ie456
WHERE ie456.event = 456
AND NOT EXISTS (
SELECT *
FROM item_events ie890
WHERE ie890.item_code = ie456.item_code
AND ie890.event = 890
AND ie890.date >= ie456.date
)
)

输出:

item2
item4