PLSQL中的自连接

Question

我有一个表P，它有一个personid列和一个licensetypeid列。

每个人可以有多个许可证类型，为该人员ID向表中添加额外的行。

我需要找到personid的许可证I同时为1和5的行。

我不能写：

SELECT personid, licensetypeid 
FROM P 
WHERE licensetypeid=1 AND licensetypeid=5;

我听说我应该使用self-join来做这件事。我如何做一个自连接来解决这个问题？

Answer 1

多次访问表的自连接和其他技术可以工作，但如果需要泛化到更大的ids集，则可能会降低性能，并且不实用。

您可以通过计算每个人的匹配行数来使用对表的单个引用来完成此操作：

select personid from P
  where licensetypeid in ('1','5')
  group by personid
  having count(*) = 2

如果您需要更大的licensettypeid值，可以很容易地进行扩展：

select personid from P
  where licensetypeid in ('1','5','7')
  group by personid
  having count(*) = 3

(在自连接版本中，您必须为每个附加值添加一个附加连接)

或者，如果你想从一个更大的类型集合中找到至少有两种类型的人：

select personid from P
  where licensetypeid in ('1','5', '7', '10')
  group by personid
  having count(*) >= 2

现在，与示例查询不同，licensetypeid不包括在结果集中。如果出于某种原因需要这样做，您可以在两个值的情况下使用一个简单的技巧：

select personid, min(licensetypeid) licensetype1, max(licensetypeid) licensetype2
  from P
  where licensetypeid in ('1','5')
  group by personid
  having count(*) = 2

但更一般的方法是将这些值分组到一个简单的集合中：

select personid, collect(licensetypeid) licensetypeidlist
  from P
  where licensetypeid in ('1','5')
  group by personid
  having count(*) = 2

Answer 2

select personid, licensetypeid
from P P1
where exists (
   select 1
   from P P2
   where P2.personid = P1.personid
      and P2.licensetypeid = 1
) and exists (
   select 1
   from P P2
   where P2.personid = P1.personid
      and P2.licensetypeid = 5
)

Answer 3

SELECT distinct
       p1.personid
      ,p1.licensetypeid 
      ,p2.licensetypeid
from P p1, P p2
WHERE p1.personid = p2.personid
AND   p1.licensetypeid = 1
AND   p2.licensetypeid = 5
;