2023年6月2日 01:51:32go评论90阅读模式

英文:

Postgresql Need a query that gives me all the parents that don't have child with a specific status value

问题

这是名为route的父表格

id	start_day	end_day
1	2023/05/01	2023/05/07
2	2023/05/01	2023/05/07
3	2023/05/01	2023/05/07
4	2023/05/01	2023/05/07
5	2023/05/01	2023/05/07

名为route_detail的子表格

id	route_id	visit_status	point_of_delivery_plant_name	point_of_delivery_plant_number
1	1	5	CROP SOLUTIONS S.A.	563
2	1	5	CROP SOLUTIONS S.A.	563
3	1	5	CROP SOLUTIONS S.A.	563
4	2	0	SAMA S.A.	781
5	3	0	WALTER SAMA HARMS	732
6	4	5	AGROSER S.A.	242
7	4	5	AGROSER S.A.	242
8	5	5	AGROFERTIL S.A	287
9	5	5	AGROFERTIL S.A	287
10	5	5	AGROFERTIL S.A	287

还有名为event的第三个子表格，对于每个route_detail记录，有一个event。这是与route_detail相关的子表格

id	route_detail_id	event_type	event_description
50	1	1	start visit
51	2	2	recurrent form
52	3	3	end visit
53	4	1	start visit
54	5	1	start visit
55	6	1	start visit
56	7	2	recurrent form
57	8	1	start visit
58	9	2	recurrent form
59	10	4	harvest advance

我尝试的是获取所有具有visit_status = 5且没有event_type = 3(end visit)事件的路线，但我无法获得该结果。

我尝试了一些类似以下的查询，但查询仍然返回具有event_type = 3的route和route_detail：

SELECT r.id,
       r.start_day,
       r.end_day,
       de.point_of_delivery_plant_name,
       de.point_of_delivery_plant_number,
       de.visit_status
FROM route r
JOIN route_detail de ON de.route_id = r.id
WHERE NOT EXISTS (SELECT 1 
                  FROM route ro 
                  JOIN route_detail rd ON rd.route_id = ro.id 
                  JOIN event ev ON ev.route_detail_id = rd.id 
                  WHERE rd.route_id = r.id 
                    AND ev.event_type_id !=7 
                    AND rd.visit_status = '5' 
                    AND rd.id = de.id)
   AND de.visit_status = '5'
GROUP BY 1,2,3,4,5,6
ORDER BY r.id DESC;

这是我的结果应该看起来的方式，因为只有路线4和5具有visit_status = '5'，并且它们的route_detail没有event_type = 3：

注意: 我没有制作表格

id	start_day	end_day
4	2023/05/01	2023/05/07
5	2023/05/01	2023/05/07

英文:

This is the parent table named route

id	start_day	end_day
1	2023/05/01	2023/05/07
2	2023/05/01	2023/05/07
3	2023/05/01	2023/05/07
4	2023/05/01	2023/05/07
5	2023/05/01	2023/05/07

Child table named route_detail

id	route_id	visit_status	point_of_delivery_plant_name	point_of_delivery_plant_number
1	1	5	CROP SOLUTIONS S.A.	563
2	1	5	CROP SOLUTIONS S.A.	563
3	1	5	CROP SOLUTIONS S.A.	563
4	2	0	SAMA S.A.	781
5	3	0	WALTER SAMA HARMS	732
6	4	5	AGROSER S.A.	242
7	4	5	AGROSER S.A.	242
8	5	5	AGROFERTIL S.A	287
9	5	5	AGROFERTIL S.A	287
10	5	5	AGROFERTIL S.A	287

and a third child table named event, for each record route_detail there is 1 event. This is child to route_detail

id	route_detail_id	event_type	event_description
50	1	1	start visit
51	2	2	recurrent form
52	3	3	end visit
53	4	1	start visit
54	5	1	start visit
55	6	1	start visit
56	7	2	recurrent form
57	8	1	start visit
58	9	2	recurrent form
59	10	4	harvest advance

What I'm trying to do is to get all the routes with visit_status = 5 and that don't have events with event_type = 3(end visit)
But I can't manage to get that result

I tried something like this after some research but the queries would still return routes with route_details with the event_type = 3 on them

SELECT r.id,
       r.start_day,
       r.end_day,
       de.point_of_delivery_plant_name,
       de.point_of_delivery_plant_number,
       de.visit_status
FROM route r
JOIN route_detail de ON de.route_id = r.id
WHERE NOT EXISTS (SELECT 1 
                  FROM route ro 
                  JOIN route_detail rd ON rd.route_id = ro.id 
                  JOIN event ev ON ev.route_detail_id = rd.id 
                  WHERE rd.route_id = r.id 
                    AND ev.event_type_id !=7 
                    AND rd.visit_status = &#39;5&#39; 
                    AND rd.id = de.id)
   AND de.visit_status = &#39;5&#39;
GROUP BY 1,2,3,4,5,6
ORDER BY r.id DESC;

This is how my results should look like, since only routes 4 and 5 have visit_status = '5' and their route_details don't have event_type =3

Note: I didn't make the tables

id	start_day	end_day
4	2023/05/01	2023/05/07
5	2023/05/01	2023/05/07

答案1

得分: 2

如果要使用EXISTS表达式来实现，您可以使用以下方式：

一个EXISTS用于检查route_detail.visit_status = 5的存在性
一个EXISTS用于在route_detail.visit_status = 5时检查event.event_type = 3的不存在性

SELECT r.*
FROM route r
WHERE EXISTS(SELECT 1
             FROM route_detail rd
             WHERE r.id = rd.route_id
               AND rd.visit_status = 5 )
  AND NOT EXISTS(SELECT 1 
                 FROM route_detail rd
                 INNER JOIN "event" e
                         ON rd.id = e.route_detail_id
                 WHERE r.id = rd.route_id
                   AND e.event_type = 3)

输出：

id	start_day	end_day
4	2023-05-01T00:00:00.000Z	2023-05-07T00:00:00.000Z
5	2023-05-01T00:00:00.000Z	2023-05-07T00:00:00.000Z

在此处查看演示 1。

英文:

If you want to do it with the EXISTS expression, you can use:

one EXISTS to check the existence of route_detail.visit_status = 5
one EXISTS to check the non-existence of event.event_type = 3 when route_detail.visit_status = 5

SELECT r.*
FROM route r
WHERE EXISTS(SELECT 1
             FROM route_detail rd
             WHERE r.id = rd.route_id
               AND rd.visit_status = 5 )
  AND NOT EXISTS(SELECT 1 
                 FROM       route_detail rd
                 INNER JOIN &quot;event&quot;      e
                         ON rd.id = e.route_detail_id
                 WHERE r.id = rd.route_id
                   AND e.event_type = 3)

Output:

id	start_day	end_day
4	2023-05-01T00:00:00.000Z	2023-05-07T00:00:00.000Z
5	2023-05-01T00:00:00.000Z	2023-05-07T00:00:00.000Z

Check the demo here.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

Postgresql Need a query that gives me all the parents that don't have child with a specific status value

问题

答案1

ORA-22806: 使用 JSON 路径项函数时不是对象或 REF。

GROUP BY 在 BigQuery 中为什么会给我一个过高的行数？

如何优化我的查询或代码以在我的C#asp.net搜索页面上使用。

将一个表中某列的唯一值复制到另一个具有不同结构的表中。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。