SQL子查询替代INTERSECT的方法

Question

我有两个表格：P和PC（通过列Id进行主/细节连接）。

Table P:
Id integer
Name varchar(12)

Table PC:
Id   integer
Code varchar(12)
Val  number

我想获取满足以下同时条件的P中所有名称：

• 拥有一个PC，该PC.Code为'A'并且Val>100

• 拥有另一个PC，该PC.Code为'B'并且Val>80

总之，我只对符合这两个条件的P.Name感兴趣。有没有一种方法可以在不使用INTERSECT的情况下进行选择？

INTERSECT查询如下：

Select P.Name 
  from P, PC
 where P.Id=PC.Id
   and PC.Code='A' and Val>100
INTERSECT
Select P.Name 
  from P, PC
 where P.Id=PC.Id
   and PC.Code='B' and Val>80

这里的目的是检查性能并允许查询在Access中运行。

- user1165334

---

这篇文章向您展示如何使用JOIN来通用地重写INTERSECT查询。具体而言，请参见Vinko Vrsalovic的解决方案。 - mwigdahl

Answer 1

不知道性能如何...试一下吧...

SELECT P.Name 
  FROM P
  INNER JOIN PC AS a ON P.Id=a.Id and a.Cod='A' and a.Val>100
  INNER JOIN PC AS b ON P.Id=b.Id and a.Cod='B' and a.Val>80

- rauschen

Answer 2

这里有一种替代方法，它在关系上是等价的（即消除重复行）：

SELECT P.Name 
  FROM P
 WHERE EXISTS (
               SELECT * 
                 FROM PC
                WHERE P.Id = PC.Id
                      AND PC.Code ='A' 
                      AND PC.Val > 100
              )
      AND EXISTS (
                  SELECT * 
                    FROM PC
                   WHERE P.Id = PC.Id
                         AND PC.Code ='B' 
                         AND PC.Val > 80
                 );

这里有几个语义上等同的替代方案（它们可能会返回重复的行）：

SELECT P.Name 
  FROM P, PC
 WHERE P.Id = PC.Id
       AND PC.Code ='A' 
       AND PC.Val > 100
       AND P.Name IN (
                      SELECT P1.Name 
                        FROM P AS P1, PC AS PC1
                       WHERE P1.Id = PC1.Id
                         AND PC1.Code = 'B' 
                             AND PC1.Val > 80
                     );
SELECT P.Name 
  FROM P, PC
 WHERE P.Id = PC.Id
       AND PC.Code ='A' 
       AND PC.Val > 100
       AND P.Name = ANY (
                         SELECT P1.Name 
                           FROM P AS P1, PC AS PC1
                          WHERE P1.Id = PC1.Id
                            AND PC1.Code = 'B' 
                                AND PC1.Val > 80
                        );

- onedaywhen

Answer 3

SELECT P.Name
  FROM P
  JOIN PC AS P1 ON P.Id = P1.Id AND P1.Cod = 'A' AND P1.Val > 100
  JOIN PC AS P2 ON P.Id = P2.Id AND P2.Cod = 'B' AND P2.Val >  80

使用表别名P1和P2可以进行三方连接。但这并不是自连接。

- Jonathan Leffler

Answer 4

Select p.Name
from P p
inner join PC pc1 on p.Id = pc1.Id and pc1.Cod = 'A' and pc1.Val > 100
inner join PC pc2 on p.Id = pc2.Id and pc2.Cod = 'B' and pc2.Val > 80

- aF.

---

不行，这样做不行，因为PC.Cod不能同时是'A'和'B'。你需要再进行一次连接。 - mwigdahl

好的，我会给你点赞来抵消那个踩你的人... :) - mwigdahl

您可以再次点击踩按钮，它就会恢复正常了 :P 感谢您的提醒！ - aF.

我本来会这样做的，只是一开始我没有给你点踩！ - mwigdahl

Answer 5

Select P.Name 
  from P, PC
 where P.Id=PC.Id
   and PC.Cod='A' and Val>100
   and exists (Select 1 From PC Where Id = P.Id and Cod = 'B' and Val > 80)

- StevieG

---

你在Access中测试过这个吗？我问这个的原因是Val和Name是保留字。 - onedaywhen

Answer 6

虽然不会真的使用这个，但有一个替代方案...

SELECT P.Name
FROM   P
       JOIN PC
         ON P.Id = PC.Id
WHERE  PC.Cod IN ( 'A', 'B' )
       AND Val > 80
GROUP  BY P.Id,
          P.Name
 HAVING MAX(CASE WHEN PC.Cod='A' and Val>100 THEN 1 END) = 1 
 AND MAX(CASE WHEN PC.Cod='B' and Val>80 THEN 1 END) = 1 

或者对于Microsoft Access，having子句需要是：

 HAVING MAX(IIf(PC.Cod='A' and Val>100, 1, 0)) = 1 
 AND MAX(IIf(PC.Cod='B' and Val>80, 1, 0)) = 1 

- Martin Smith

---

不是通过贬低，而是非常扭曲。虽然它可以工作，但几乎不能直接满足要求的翻译。 - Jonathan Leffler

@JonathanLeffler - OP要求提供替代方案。这个方法仅通过表格进行一次遍历。 - Martin Smith