当前位置：文江博客话题详情

查询检查记录的一致性

发布于 2024-09-06 18:03:37 字数 384 浏览 0 评论 0原文

我有四个表

TableA:

id1
id2
id3
value

TableB:

id1
desc

TableC:

id2
desc

TableD:

id3
desc

我需要做的是检查表 BC 和 D 中 id1 id2 id3 的所有组合是否存在于 TableA 中。换句话说，表A应该包含id1、id2和id3的所有可能组合，这些组合存储在其他三个表中。

原文

分享到QQ

分享到微博

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

发布评论

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

柠檬 2024-09-13 18:03:37

此查询评估 id1、id2 和 id3（交叉联接）的所有组合，并查找表 a 中不存在的组合。

select b.id1, c.id2, d.id3 from
 TableB b cross join TableC c cross join TableD d WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3=d.id3)

编辑：使用 RIGHT JOIN

SELECT allPerms.id1, allPerms.id2, allPerms.id3 FROM a RIGHT JOIN (select b.id1, c.id2, d.id3 from
 TableB b cross join TableC c cross join TableD) allPerms
 ON a.id1=allPerms.id1 AND a.id2=allPerms.id2 AND a.id3=allPerms.id3
 WHERE a.id1 IS NULL

两者几乎相同。由于我们实际上并未从连接表中获取值，因此有些人更喜欢第一种方法，因为它捕获了查询的意图和精神。第二个版本更加“面向实现”。一个好的优化器将为两者生成一个有效的计划，但在一些较小的 RDBMS 上，第二个版本将运行得更快。

使用表 D 的预定义集 - id3 具有值 (2,5,6)

select b.id1, c.id2 from
 TableB b cross join TableC c WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3 IN (2,5,6))

但是，这不会为您提供表 A 行中缺少的 id3。为此，我认为最简单的方法是通过联合模拟表，例如

select b.id1, c.id2, d.id3 from
 TableB b, TableC c, (select 2 id3 union select 5 union select 6) d 
WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3=d.id3)

（这仍然使用交叉联接 - 如果表用逗号分隔，则暗示。）

This query evaluates all combinations of id1, id2 and id3 (the cross join) and finds which combinations are not present in table a.

select b.id1, c.id2, d.id3 from
 TableB b cross join TableC c cross join TableD d WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3=d.id3)

EDIT: With an RIGHT JOIN

SELECT allPerms.id1, allPerms.id2, allPerms.id3 FROM a RIGHT JOIN (select b.id1, c.id2, d.id3 from
 TableB b cross join TableC c cross join TableD) allPerms
 ON a.id1=allPerms.id1 AND a.id2=allPerms.id2 AND a.id3=allPerms.id3
 WHERE a.id1 IS NULL

The two are pretty much the same. Since we are not actually fetching values from the joined table, some people prefer the first approach, since it captures the intent and spirit of the query. The second version is more "implementation oriented". A good optimizer will produce an efficient plan for both, but on some lesser RDBMSs, the second version will run faster.

With a predefined set for table D - id3 has values (2,5,6)

select b.id1, c.id2 from
 TableB b cross join TableC c WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3 IN (2,5,6))

But, this doesn't give you the id3 that is missing in the table A row. For that, I think the simplest is to emulate the table via a union, e.g.

select b.id1, c.id2, d.id3 from
 TableB b, TableC c, (select 2 id3 union select 5 union select 6) d 
WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3=d.id3)

(This is still using cross join - it's implied if tables are separated by commas.)

回复收藏 0 原文

~没有更多了~