如何为联接的“列表"表实现“每次"和“不"操作 [英] How to implement EVERY and NO operations for a joined 'list' table

问题描述

我有两个表Person和_Person_Name，其中包含一个列Name和一个列Person.Id作为外键.我正在寻找两个搜索操作，分别称为每个和否.

I have two tables Person and _Person_Name which contains a column Name and a column Owner with Person.Id as foreign key. I'm looking for two search operations I'd call EVERY and NO.

1.)每个

1.) Every

以下仅返回与查询 LIKE'％n％'相匹配的 all 对应名称的人员ID:

The following returns only the Person IDs for which all corresponding names match for query LIKE '%n%':

SELECT Person.Id as Result FROM Person
INNER JOIN _Person_Name AS __T1 ON Person.Id = __T1.Owner 
WHERE __T1.Name LIKE '%n%'
GROUP BY Result
HAVING Count(Result)=(Select Count(*) FROM _Person_Name 
  WHERE Person.Id=_Person_Name.Owner)

但是问题是，我还必须处理其他查询，这些查询仅需一个匹配就足够了，并且HAVING子句适用于WHERE子句中的所有术语.

But the problem is that I also have to deal with other queries for which just a single match suffices, and the HAVING clause applies to all terms in the WHERE clause.

是否有办法获得与上述查询中的HAVING子句相同的效果，但可以在WHERE子句中以某种方式表示，以便可以向其添加其他普通条件?

Is there a way to get the same effect as in the HAVING clause in the above query, but somehow express this within the WHERE clause such that other, ordinary conditions can be added to it?

示例:

SELECT Person.Id as Result FROM Person 
INNER JOIN _Person_Name AS __T1 ON Person.Id = __T1.Owner
WHERE (EVERY __T1.Name LIKE '%n%') 
OR (__T1.Name LIKE 'John')

第二个析取符应该以普通方式运行而没有任何限制.现在，我正在寻找一种表达每个的方法，就像上面的 HAVING 子句中那样.每当具有匹配所有者的一个字段具有名称字段 __ T1.Name LIKE'John'时，查询应返回 __ T1.Owner (= Person.Id)或所有者匹配的 all __ T1.Name 字段包含"n"的情况.

The second disjunct should just behave in the ordinary way without any restrictions. Now I'm looking for a way to express EVERY like in the above HAVING clause. The query should return __T1.Owner (=Person.Id) whenever it is the case that one field with matching owner has a name field __T1.Name LIKE 'John' or it is the case that all __T1.Name fields with matching owner contain an 'n'.

如果无法在WHERE子句中表达这一点，怎么表达呢?

If it is not possible to express this in the WHERE clause, how can it be expressed?

2.)否

2.) No

NO 搜索操作类似于 EVERY ，但是基本条件被否定，即，我正在寻找与之关联的名称部分均不匹配的人员查询.我想如果我有 EVERY 的解决方案，我就能轻松实现.

The NO search operation is just like EVERY but the base condition is negated, i.e., I'm looking for the Persons for which none of their associated Name parts matches the query. I suppose I can get that easily if I have a solution for EVERY.

推荐答案

让我以否定的想法开始:如果您按条件'％n％'查找，则可以检查是否不存在任何行.您可以在WHERE子句中使用EXISTS子查询来做到这一点:

Let me start with an idea for NO: you can check if no row exists if you look up by condition LIKE '%n%'. You can do it in the WHERE clause with EXISTS subquery:

WHERE
    NOT EXISTS (
        SELECT 1 FROM _Person_Name AS __T2 WHERE Person.Id = __T2.Owner
        and __T2.Name LIKE '%n%'
        )
OR (__T1.Name LIKE 'John')

NOT EXISTS检查是否不存在匹配项.

NOT EXISTS checks if there is NO match.

基于此，对每个人来说，实现都是要查看对无行条件的求反是否成立，因此您正在寻找不喜欢'％n％'的方法:

Based on this the implementation for EVERY is to see if the negation of your condition for no row is true, so you are looking for NOT LIKE '%n%':

WHERE
    NOT EXISTS (
        SELECT 1 FROM _Person_Name AS __T2 WHERE Person.Id = __T2.Owner
        and __T2.Name NOT LIKE '%n%'
        )
OR (__T1.Name LIKE 'John')

这次，NOT EXISTS将确保没有发现不符合您条件的行-因此，每一行都与之匹配.

This time NOT EXISTS will make sure no rows found that does NOT match your criteria - therefore EVERY row matched it.

这篇关于如何为联接的“列表"表实现“每次"和“不"操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！