除了或联合关系代数的SQL Server查询

I am trying to solve a problem. It seems that of a brain teaser if you ask me.

我想解决一个问题。如果你问我的话，我觉得这是一个脑筋急转弯。

Given two tables, return only values from the first table when there is a match for EVERY record in a second table. So a record in table 1 must have a match to every record in table 2. If table 2 has fewer than every row I want to exclude it from the final result.

给定两个表，当第二个表中的每个记录都匹配时，只从第一个表返回值。因此，表1中的记录必须与表2中的每个记录匹配。如果表2的行数少于每一行，我想把它从最终结果中排除。

This must be done without using count, having, group by. I must solve it with union, intersect, except, exists.

这必须在不使用count、having、group by的情况下完成。我必须用联合来解决它，相交，除了，存在。

I am using SQL Server BTW.

顺便说一句，我正在使用SQL Server。

CREATE TABLE table1 (id int, lid int)
INSERT INTO table1  VALUES (1, 1),(1, 2),(1,3),(1,4),(2,1),(3,3),(4,4)

CREATE TABLE table2 (lid int)
INSERT INTO table2 VALUES (1),(2),(3),(4)

Table 1:

表1:

id  lid
--------
1   1
1   2
1   3
1   4
2   1
3   3
4   4

Table2:

表二:

lid
-----
1
2
3
4

This method here is "not the way I am supposed to solve it". Frustrating because this solution is so simple and does exactly what it should do. I can't use count, group by, and having.

这里的方法是“不是我应该解的方法”。令人沮丧的是，这个解决方案是如此简单，并且做了它应该做的事情。我不能用count, group by和having。

SELECT id 
FROM dbo.table1, dbo.table2
WHERE table1.lid = table2.lid
GROUP BY id
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.table2)

So basically I need to find a way to exclude the results from the first table when there is not a full set of matches in table 2. In this example the only value in table 1 with a match to every record in table 2 is 1. 2,3,4 would need to be excluded.

因此，基本上我需要找到一种方法，在表2中没有完整的匹配集时，从第一个表中排除结果。在本例中，表1中与表2中的每个记录匹配的唯一值是1。需要排除2,3,4。

2 个解决方案

#1

What you're looking for has a name. It's called relational division. It has no equivalent in SQL, although it can be emulated in a variety of ways. Joe Celko has written one of the most complete blog posts about the topic.

你要找的是有名字的。它叫做关系部门。它在SQL中没有对应的功能，尽管可以通过多种方式进行模拟。Joe Celko写了一篇关于这个话题的最完整的博文。

Since you must use some of the more basic relational operators in SQL, this could be one solution for you:

由于您必须使用SQL中的一些更基本的关系操作符，这可能是您的一个解决方案:

SELECT DISTINCT id
FROM table1 t1a
WHERE NOT EXISTS (
  SELECT *
  FROM table2
  WHERE NOT EXISTS (
    SELECT *
    FROM table1 t1b
    WHERE t1a.id = t1b.id
    AND t2.lid = t1b.lid
  )
)

It reads in English, informally:

它是用英语写的，非正式地:

Get me all the elements in table1 for which there is no element in table2, which doesn't match such an element from table1

获取表1中所有表2中没有元素的元素，这些元素与表1中的元素不匹配

Or also:

或者:

Get me the elements from table1, which match all the elements in table2

从表1中获取元素，它匹配表2中的所有元素

#2

That's one of the solutions:

这是解决方法之一:

select distinct id from table1 AS T1
where not exists(
    select lid from table2
    except 
    select lid from table1 where id = T1.id
)

#1