postgresql 中可以有多个横向交叉连接吗？

发布于 2025-01-11 08:35:16 字数 1292 浏览 0 评论 0原文

PostgreSQL 中的一个查询中是否可以有多个横向交叉联接？

我面临的问题是我将 json_array_element 交叉连接到一些 UUID 值，如下所示，

 CROSS JOIN LATERAL json_array_elements_text(dep."JSON_ID" -> 'staffdepartmentId') AS j(data)
 WHERE j.data :: uuid NOT IN (  '1b381940-1df1-4ced-8b66-baad6e8516ee',
                                'c690d00c-9a57-11ec-b909-0242ac120002',
                                'c690ddf4-9a57-11ec-b909-0242ac120002',
                                '404429ae-ec74-42c0-8e25-0f23b5f88a11',
                                'fe701d28-377b-450e-8526-f949b9df0eb5'
                             )

因此，如果可能的话，我需要如上所述进行另一个交叉连接，

 CROSS JOIN LATERAL json_array_elements_text(dep."JSON_ID" -> 'studentdepartmentId') AS s(data)
 WHERE s.data :: uuid NOT IN  
                 (select st.id 
                       from student.records st
                        where st.id
                        in ( 'b8ba080a-7fb3-4638-af94-d177b73e44f4,
                             '0ff8bc79-6a1a-4669-a4c2-8504f3879ca2',
                             'e3447829-0b4d-4938-a373-f6669747f24b',
                             '6500b90b-a652-4f2e-a248-d57db238c0e9'
                             ))

显然我不能在同一查询中使用两个 where 子句。但我不确定如何在没有交叉连接横向的情况下执行第二个交叉连接查询。非常感谢任何帮助

原文

Is it possible to have multiple lateral cross joins in one query in PostgreSQL?

The problem I am facing is I am cross joining a json_array_element to some UUID values as below,

 CROSS JOIN LATERAL json_array_elements_text(dep."JSON_ID" -> 'staffdepartmentId') AS j(data)
 WHERE j.data :: uuid NOT IN (  '1b381940-1df1-4ced-8b66-baad6e8516ee',
                                'c690d00c-9a57-11ec-b909-0242ac120002',
                                'c690ddf4-9a57-11ec-b909-0242ac120002',
                                '404429ae-ec74-42c0-8e25-0f23b5f88a11',
                                'fe701d28-377b-450e-8526-f949b9df0eb5'
                             )

So, I need to do another cross join as above if possible,

 CROSS JOIN LATERAL json_array_elements_text(dep."JSON_ID" -> 'studentdepartmentId') AS s(data)
 WHERE s.data :: uuid NOT IN  
                 (select st.id 
                       from student.records st
                        where st.id
                        in ( 'b8ba080a-7fb3-4638-af94-d177b73e44f4,
                             '0ff8bc79-6a1a-4669-a4c2-8504f3879ca2',
                             'e3447829-0b4d-4938-a373-f6669747f24b',
                             '6500b90b-a652-4f2e-a248-d57db238c0e9'
                             ))

Obviously I cannot use two where clause in the same query. But I am not sure how to do the second cross join query without a cross join lateral. Any help is highly appreciated

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

隐诗 2025-01-18 08:35:16

您可以通过将其中一个查询（或两个查询）分解为 CTE 并添加索引列来解决您的问题。然后根据需要在索引上将数据连接在一起。每个 CTE 肯定能够拥有自己的横向交叉连接。

例子：

with cte1 as (
    <insert query1 here>
)
, cte2 as (
    <insert query2 here>
)

select *
from table1
left join cte1 on cte1.index = table1.index
left join cte2 on cte2.index = table1.index

You may be able to solve your problem by breaking out one of the queries (or both) into a CTE and adding an index column. Then join the data together as needed on the index. Each CTE would certainly be able to have it's own lateral cross join.

Example:

with cte1 as (
    <insert query1 here>
)
, cte2 as (
    <insert query2 here>
)

select *
from table1
left join cte1 on cte1.index = table1.index
left join cte2 on cte2.index = table1.index

回复收藏 0 原文

~没有更多了~