改进 mysql 查询

发布于 2024-09-11 12:51:41 字数 667 浏览 6 评论 0原文

我有一个疑问。

DELETE FROM A  
WHERE i NOT IN 
( SELECT i FROM B WHERE j = 1  
  UNION select i from C 
  UNION select i from D 
);

基本上删除 A 中字段 i 未出现在表 B、C 或 D 中的所有行。如果只是:

DELETE FROM A  
WHERE i NOT IN 
( SELECT i FROM B 
);

那么可以使用左连接轻松完成

DELETE A FROM A 
LEFT JOIN B 
ON A.i = B.i
WHERE B.id is NULL;

(假设每个表都有一个 id模式中的字段)

我想我的问题是上面的内容是否扩展到 三表场景有以下解决方案吗?

DELETE A FROM A 
LEFT JOIN B 
ON A.i = B.i AND B.j = 1
LEFT JOIN C
ON A.i = C.i 
LEFT JOIN D
ON A.i = D.i
WHERE B.id is NULL
AND   C.id is NULL
AND   D.id is NULL

I have a query.

DELETE FROM A  
WHERE i NOT IN 
( SELECT i FROM B WHERE j = 1  
  UNION select i from C 
  UNION select i from D 
);

Basically delete all rows in A where field i does not occur in tables B, C or D. If it was just:

DELETE FROM A  
WHERE i NOT IN 
( SELECT i FROM B 
);

Then that could be done easily with a left join

DELETE A FROM A 
LEFT JOIN B 
ON A.i = B.i
WHERE B.id is NULL;

( Assume that every table has a id field in the schema )

I guess my question is then does the above extend to
the three table scenario with the following solution?

DELETE A FROM A 
LEFT JOIN B 
ON A.i = B.i AND B.j = 1
LEFT JOIN C
ON A.i = C.i 
LEFT JOIN D
ON A.i = D.i
WHERE B.id is NULL
AND   C.id is NULL
AND   D.id is NULL

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

豆芽 2024-09-18 12:51:41

像这样的事情:

DELETE 
  FROM A
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM B
                    WHERE B.i = A.i
                  )
       AND NOT EXISTS (
                       SELECT * 
                         FROM C
                        WHERE C.i = A.i
                      )
       AND NOT EXISTS (
                       SELECT * 
                         FROM D
                        WHERE D.i = A.i
                      );

Something like this:

DELETE 
  FROM A
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM B
                    WHERE B.i = A.i
                  )
       AND NOT EXISTS (
                       SELECT * 
                         FROM C
                        WHERE C.i = A.i
                      )
       AND NOT EXISTS (
                       SELECT * 
                         FROM D
                        WHERE D.i = A.i
                      );
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文