gpt4 book ai didi

sql - 简化/重用此 SQL 查询中的子查询

转载 作者:行者123 更新时间:2023-11-29 14:04:44 28 4
gpt4 key购买 nike

我有以下 SQL 查询:

SELECT "users".* FROM "users"
WHERE (
users.id IN (SELECT manager_id FROM managerships WHERE managerships.employee_id = 1234) OR
users.custom_id IN (SELECT manager_custom_id FROM managerships WHERE managerships.employee_id = 1234) OR
users.saml_id IN (SELECT manager_saml_id FROM managerships WHERE managerships.employee_id = 1234) OR
users.email IN (SELECT manager_email FROM managerships WHERE managerships.employee_id = 1234)
)

现在我相信应该有一种方法可以从我的 managerships 表中选择一次并重用它,比如:

SELECT manager_id, manager_email, manager_custom_id, manager_saml_id FROM managerships WHERE managerships.employee_id = 1234

我怎样才能做到这一点?谢谢!

最佳答案

您可以使用 WITH 将聚合取出到 CTE(公用表表达式)中:

WITH m AS (SELECT manager_id
,manager_email
,manager_custom_id
,manager_saml_id
FROM managerships
WHERE managerships.employee_id = 1234
)
SELECT "users".* FROM "users"
WHERE (
users.id IN (SELECT manager_id FROM m) OR
users.custom_id IN (SELECT manager_custom_id FROM m) OR
users.saml_id IN (SELECT manager_saml_id FROM m) OR
users.email IN (SELECT manager_email FROM m)
);

另请引用https://www.postgresql.org/docs/9.1/static/queries-with.html

关于sql - 简化/重用此 SQL 查询中的子查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43197110/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com