MSSQL查询:如何按每个分区查找不正确的行

Question

我需要根据逻辑查找不正确的行。

逻辑是：

1. 如果孩子有行(我将调用第一行(

   | merit     | fruit | vegetable |
   | --------- | ----- | --------- |
   | behaviour | apple | cucumber  |
   

   则在具有优点=诗和水果=苹果的行中，必须仅蔬菜=黄瓜(黄瓜，没有其他单词((这是第二行(

   | merit | fruit | vegetable |
   | ----- | ----- | --------- |
   | poem  | apple | cucumber  |
   

2. 第二行的AND时间间隔必须比第一行的时间早4小时或晚4小时，例如：

   | child_id  | date            | merit     | fruit | vegetable |
   | --------- | --------------- | --------- | ----- | --------- |
   | 2         | 1/26/2022 16:00 | poem      | apple | cucumber  |
   | 2         | 1/26/2022 18:00 | behaviour | apple | cucumber  |
   

   正如我们所看到的，它是在4小时间隔

我有表格：

| child_id  | date            | merit       | fruit   | vegetable |
| --------- | --------------- | ----------- | ------- | --------- |
| 1         | 1/27/2022 14:00 | behaviour   | apple   | cucumber  |
| 1         | 1/27/2022 15:00 | poem        | apple   | carrot    |
| 1         | 1/27/2022 17:00 | sleep       | apple   | ginger    |
| 1         | 1/27/2022 20:00 | competition | berry   | tomatoe   |
| 2         | 1/26/2022 13:00 | sleep       | apricot | tomatoe   |
| 2         | 1/30/2022 13:00 | poem        | apple   | cucumber  |
| 2         | 1/29/2022 13:00 | poem        | apple   | cucumber  |
| 2         | 1/26/2022 16:00 | poem        | apple   | cucumber  |
| 2         | 1/26/2022 18:00 | behaviour   | apple   | cucumber  |
| 2         | 1/26/2022 19:00 | present     | apple   | broccoli  |
| 3         | 1/25/2022 11:00 | present     | orange  | cucumber  |
| 3         | 1/25/2022 13:00 | poem        | apple   | ginger    |
| 3         | 1/25/2022 15:00 | behaviour   | apple   | cucumber  |
| 4         | 1/26/2022 14:00 | behaviour   | apple   | cucumber  |
| 4         | 1/27/2022 21:00 | poem        | apple   | carrot    |
| 4         | 1/27/2022 15:00 | poem        | apple   | carrot    |
| 4         | 1/27/2022 20:00 | sleep       | apple   | ginger    |
| 4         | 1/27/2022 21:00 | competition | berry   | tomatoe   |

我期待的结果是：

| child_id  | date             | merit | fruit | vegetable |
| --------- | --------------- | ----- | ----- | --------- |
| 1         | 1/27/2022 15:00 | poem  | apple | carrot    |
| 3         | 1/25/2022 13:00 | poem  | apple | ginger    |

我不知道如何按孩子查找这些行。我写了这个SQL并坚持：

select * from example_1 where merit in ('behaviour', 'poem') 

我这里需要隔板吗？

Answer 1

一个潜在的解决方案是使用LEFT OUTER JOIN将表连接到自身，然后只接受连接版本的表返回null的记录：

SELECT e1.* 
FROM example_1 e1
LEFT OUTER JOIN example_1 e2
ON e1.fruit = e2.fruit
AND e1.vegetable <> e2.vegetable
AND e2.date BETWEEN DATEADD(HOUR, -4, e1.date) AND e1.date
AND e2.merit = 'behavior'
WHERE e1.merit = 'poem'
AND e2.child_id IS NULL 

技巧主要在于连接标准，我们希望确保在"行为"one_answers"诗歌"之间匹配vegetable，同时检查最后4个小时。

Answer 2

在这种方法中，我们使用一个经过整理的子查询。顶部查询B定义了所需结果的数据的非联接限制。所以蔬菜<gt；黄瓜与功绩=诗歌

Exists确保定义了第一行的限制，并且存在不匹配的相关性。因此，我们确保水果匹配，优点是behavior，childid匹配，日期差异在4小时内。

DEMO-DB Fiddle UK

SELECT B.* 
FROM table B
WHERE vegetable <> 'cucumber'
and merit = 'poem'
and exists (SELECT 1 
FROM Table A
WHERE A.Fruit = B.Fruit
AND A.Child_id = B.Child_ID
AND A.merit = 'behaviour' 
AND abs(Datediff(hour,A.Date,B.Date)) <=4)

给我们：

+----------+-------------------------+-------+-------+-----------+
| child_id |          date           | merit | fruit | vegetable |
+----------+-------------------------+-------+-------+-----------+
|        1 | 2022-01-27 15:00:00.000 | poem  | apple | carrot    |
|        3 | 2022-01-25 13:00:00.000 | poem  | apple | ginger    |
+----------+-------------------------+-------+-------+-----------+