PLSQL 中的自连接

Question

我有一个表 P，其中包含 personid 列和 licensetypeid 列。

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

我需要找到 personid 的许可证 ID 分别为 1 和 5 的行。

我无法编写：

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

我听说我应该使用自连接来执行此操作。我该如何进行自连接来解决这个问题？

Answer 1

自连接和其他多次访问表的技术可以工作，但可能会降低性能，并且如果您需要推广到更大的 id 集，则这些技术会很笨重。

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

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

此操作：如果您想要需要更大的许可证类型ID值，则可以轻松扩展：（

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

在自连接版本中，您必须添加额外的加入每个附加值）

或者如果您想在更大的类型集中查找至少具有 2 种类型的人：

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

现在，与示例查询不同，licensetypeid 不包含在结果集中。如果出于某种原因有必要这样做，您可以在有 2 个值的情况下执行一个简单的技巧：

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
;

Answer 4

试试这个

select personid,licensetypeid from P where licensetypeid in ('1','5')

Answer 5

如果您所需的许可证类型集是“固定的”，或者至少如果所需许可证类型集的基数是固定的，则给定的答案将正常工作。

否则，您需要编写与所谓的“关系除法”等效的 SQL。

其过程如下：

(1) 计算缺少至少一种所需许可证类型的人员集合：

SELECT personid 
from P 
WHERE EXISTS
  (
    SELECT licenseid 
    from NEEDEDLICENSETYPE AS NLT 
    WHERE NOT EXISTS (
      SELECT * 
      FROM P AS PBIS 
      WHERE 
        PBIS.personid = P.personid AND
        PBIS.licensetype = NLT.licensetype
    )
  )

NEEDELICETYPE 表示在特定调用上计算所需许可证类型集合所需的任何 SQL 语句。

(2) 选择(1)中没有出现的号码的人员数据：

SELECT ... FROM P WHERE personid NOT IN (...)