在SQL中更改为具有独特的SQL

发布于 2025-01-18 10:20:09 字数 775 浏览 1 评论 0原文

拥有这个：

code IN 
(
SELECT  DISTINCT aaa.code
FROM ORGHEADER AS aaa
LEFT JOIN ORGRELATEDPARTY AS bbb
    ON aaa.oh_pk = bbb.parent
WHERE aaa.oh_pk NOT IN 
(
SELECT  fu.parent 
FROM ORGRELATEDPARTY  fu
WHERE fu.partytype = 'MNG'
)
)

阅读这个：在 SQL 中将 IN 更改为 EXISTS

尝试将其更改为“Exists”，但是产生了这个但它不起作用：

code EXISTS
(
SELECT  *
FROM ORGHEADER AS aaa
LEFT JOIN ORGRELATEDPARTY AS bbb
    ON aaa.oh_pk = bbb.pr_oh_parent
WHERE aaa.oh_pk NOT IN 
(
SELECT  fu.parent 
FROM ORGRELATEDPARTY  fu
WHERE fu.pr_partytype = 'MNG'
)
WHERE code = DISTINCT aaa.oh_code
)

错误为 3706：语法错误：需要介于“=”和“DISTINCT”关键字之间的内容。

原文

Having this one:

code IN 
(
SELECT  DISTINCT aaa.code
FROM ORGHEADER AS aaa
LEFT JOIN ORGRELATEDPARTY AS bbb
    ON aaa.oh_pk = bbb.parent
WHERE aaa.oh_pk NOT IN 
(
SELECT  fu.parent 
FROM ORGRELATEDPARTY  fu
WHERE fu.partytype = 'MNG'
)
)

Reading this one:
Changing IN to EXISTS in SQL

Tried to change it into "Exists", but produced this and it did not work:

code EXISTS
(
SELECT  *
FROM ORGHEADER AS aaa
LEFT JOIN ORGRELATEDPARTY AS bbb
    ON aaa.oh_pk = bbb.pr_oh_parent
WHERE aaa.oh_pk NOT IN 
(
SELECT  fu.parent 
FROM ORGRELATEDPARTY  fu
WHERE fu.pr_partytype = 'MNG'
)
WHERE code = DISTINCT aaa.oh_code
)

The error is 3706: Syntax error: expected something between '=' and 'DISTINCT' keyword.

分享到QQ

分享到微博

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

发布评论

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

﹏半生如梦愿梦如真 2025-01-25 10:20:10

您已经有多余的代码。
在 IN 子查询中，您从 ORGHEADER 选择一列，因此 LEFT 连接只会增加噪音，因为它在任何情况下都会返回 ORGHEADER 中的所有行code>ORGHEADER。

假设 code 属于一个别名为 t 的表，您可以使用 EXISTS 编写代码，如下所示这：

WHERE EXISTS (
  SELECT 1
  FROM ORGHEADER AS aaa
  WHERE aaa.oh_code = t.code
    AND aaa.oh_pk NOT IN (
      SELECT parent 
      FROM ORGRELATEDPARTY  
      WHERE pr_partytype = 'MNG'
    )
)

另外，不如果表 ORGRELATEDPARTY 的列 parent 可能返回 null，IN 将不起作用。

You have already redundant code.
Inside the IN subquery you are selecting a column from ORGHEADER, so the LEFT join only adds noise since it returns in any case all rows from ORGHEADER.

Assuming that code belongs to a table aliased as t you can write the code with EXISTS like this:

WHERE EXISTS (
  SELECT 1
  FROM ORGHEADER AS aaa
  WHERE aaa.oh_code = t.code
    AND aaa.oh_pk NOT IN (
      SELECT parent 
      FROM ORGRELATEDPARTY  
      WHERE pr_partytype = 'MNG'
    )
)

Also, NOT IN will not work if the column parent of the table ORGRELATEDPARTY may return nulls.

回复收藏 0 原文

~没有更多了~