复杂的 SQL where 子句 - 集体选择所有但从不重叠
编辑：我的问题是有缺陷的，因为它被误解了。我正在重新表述。最初的问题是在 HR 下面，为了好奇...
给定几个对相同数据进行操作的 SQL 查询，我如何在类型系统、对象模型或测试代码中断言每个可能行是由恰好一个查询选择：
即在这个维恩图中（感谢@Imre_L提供的图像）断言查询A、B和U的最佳方式是什么不重叠？
请注意，问题空间是所有可能数据，而不仅仅是现有数据集。
最初的问题
我正在尝试设计一种安全且易于理解的机制来组成一组 where 子句，其中所选行的子集从不重叠，但所有子句的执行将选择所有子句的集合行。
例如，给定一个子句（在伪 SQL 中）like:
... where name like 'Bob%'
and surname not like '%Smith'
and postcode in (2000, 2010, 2020)
和另一个 like
... where name in ('Alice', 'Jane')
and postcode < 9000
and married=True
以及捕获其余部分的最终查询，
... where not (name like 'Bob%' and surname not like '%Smith' and postcode in (2000, 2010, 2020))
and not (name in ('Alice', 'Jane') and postcode < 9000 and married=True)
我如何断言每个可能的行都被可能的查询之一完全选择？
我的团队正在慢慢地将行为从系统的一个部分迁移到另一个部分，并且需要在每个阶段更改语句集，以便在更改后由一个语句选择的某些行由另一个语句选择。上述断言在每次更改之前和之后都需要成立。
我正在寻找一个简洁的 Java 解决方案。无论它是在类型系统中检查，还是由某些创建模式控制，甚至在测试期间检查，都无关紧要。我确信一些研究人员有一些好主意。
EDIT: My question was deficient because it was misunderstood. I'm rephrasing it. The original question is below the HR for the curious...
Given several SQL queries that operate on the same data, how can I assert in the type system, object model or test code that each possible row is selected by exactly one of the queries:
i.e. in this Venn diagram (thank you @Imre_L for the image) what is the best way to assert that queries A, B and U do not overlap?
Note the problem space is all possible data and not just the existing dataset.
The original question
I'm trying to devise a safe and easy to understand mechanism for composing a set of where clauses where the subsets of selected rows never overlap, yet execution of all clauses would select the set of all rows.
For example given a clause (in pseudo SQL) like:
... where name like 'Bob%'
and surname not like '%Smith'
and postcode in (2000, 2010, 2020)
and another like
... where name in ('Alice', 'Jane')
and postcode < 9000
and married=True
and a final query to catch the remainder
... where not (name like 'Bob%' and surname not like '%Smith' and postcode in (2000, 2010, 2020))
and not (name in ('Alice', 'Jane') and postcode < 9000 and married=True)
How can I assert that each possible row is selected by exactly one of the possible queries?
My team is slowly migrating behaviour from one part of the system to another and need, at each stage, to alter the set of statements so that some rows selected by one statement are selected by a different one after the change. The assertion above needs to hold true before and after each change.
I'm looking for a neat solution for Java. Whether it's checked in the type system, or controlled by some creation pattern or even checked during testing it doesn't matter. I'm sure some boffins have some good ideas out there.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这是错误的 - 问题针对所有可能数据。
我不确定我是否理解，但 SQL 包含
union和distinct，因此您可以检查以下内容的计数：所有查询联合在一起
所有查询联合在一起，不同的行
表中的所有行
并且它们应该全部相等。如果完全缺少一行，则 2 将小于 3；如果您在任何地方复制一行，那么 2 将小于 1。1
和 2 的查询将是：
This is wrong - the question is for all possible data.
I am not sure I understand, but SQL includes
unionanddistinctso you could check the count for:All queries unioned together
All queries unioned together, distinct rows
All rows in the table
And they should all be equal. If you are missing a row completely then 2 will be less than 3; if you are duplicating a row anywhere then 2 will be less than 1.
The queries for 1 and 2 would be:
这个问题更多的是数学问题，是集合论。也许维恩图可以拯救你。
在您的示例中，A 和 B 是查询 1,2，U 是最后一个查询。
了解有关维恩图的更多信息
如果您期望更好的答案，您应该详细说明。
This question is more of a math question is set theory. Perhaps venn diagram is here to rescue.
In your example A and B are queries 1,2 and U is the last query.
learn more about venn diagrams
If you expect better answer you should elaborate more.
我通过手工方法在自动化测试中解决了这个问题。
我更喜欢一种涉及奇特类型层次结构的真正自动化的方法，但我们并不总是能得到我们想要的。
I solved this in automated testing with a hand-crafted approach.
I would have preferred a truly automated approach involving a fancy type hierarchy, but we can't always get what we want.