自连接的目的

Question

我正在学习使用SQL编程，并且刚刚被介绍给自连接。我知道它们是如何工作的，但是除了一个非常具体的用法之外，我不知道它们的用途是什么，将一个employee表连接到它本身来整齐地显示员工及其各自的经理。

此用法可通过下表进行演示：

EmployeeID | Name          | ManagerID
    ------ | ------------- | ------
    1      | Sam           | 10
    2      | Harry         | 4
    4      | Manager       | NULL
   10      | AnotherManager| NULL

和以下查询：

select worker.employeeID, worker.name, worker.managerID, manager.name
from employee worker join employee manager
on (worker.managerID = manager.employeeID);

它将返回：

Sam   AnotherManager
Harry Manager

除此之外，还有没有其他情况下自连接是有用的？我想不出需要执行自连接的场景。

Answer 1

你的例子是一个很好的例子。只要表本身包含外键，自联接就很有用。一个员工有一个经理，而经理是...另一个员工。因此，自连接在那里是有意义的。

许多层次结构和关系树非常适合这一点。例如，您可能将一个父组织划分为区域、组、团队和办公室。它们中的每一个都可以存储为一个“组织”，其中父id作为一列。

或者，也许你的企业有一个推荐计划，而你想记录下哪个客户推荐了某人。他们都是“客户”，在同一个表中，但其中一个有一个FK链接到另一个。

当一个实体可能有多个“父”链接时，的层次结构就不适合这样做了。例如，假设您有facebook风格的数据来记录每个用户和其他用户的友情链接。用户可以根据这个模型来创建用户，但是您需要为用户拥有的每个朋友创建一个新的“”行，并且除了"relationshipUserID“列或其他名称之外，每一行都是重复的。

在多对多关系中，您可能会有一个单独的“关系”表，其中有一个"from“和"to”列，也可能有一个指示关系类型的列。

Answer 2

我发现self join在这样的情况下最有用：

让所有为萨姆的经理工作的员工。(这不一定是分层的，也可以是:获取与Sam在同一位置工作的所有员工)

select e2.employeeID, e2.name
from employee e1 join employee e2
on (e1.managerID = e2.managerID)
where e1.name = 'Sam'

在表中查找重复项也很有用，但效率很低。

Answer 3

这里有几个使用自连接的很好的例子。我经常使用的是关于“时间表”的。我在教育中使用时间表，但它们在其他情况下也是相关的。

我使用自连接来计算两个项目是否相互冲突，例如，一个学生被安排在同一时间上两节课，或者一个房间被重复预订。例如：

CREATE TABLE StudentEvents(
    StudentId int,
    EventId int,
    EventDate date,
    StartTime time,
    EndTime time
)

SELECT
    se1.StudentId,
    se1.EventDate,
    se1.EventId Event1Id,
    se1.StartTime as Event1Start,
    se1.EndTime as Event1End,
    se2.StartTime as Event2Start,
    se2.EndTime as Event2End,
FROM
    StudentEvents se1
    JOIN StudentEvents se2 ON
        se1.StudentId = se2.StudentId
        AND se1.EventDate = se2.EventDate
        AND se1.EventId > se2.EventId 
        --The above line prevents (a) an event being seen as clashing with itself
        --and (b) the same pair of events being returned twice, once as (A,B) and once as (B,A)
WHERE
    se1.StartTime < se2.EndTime AND
    se1.EndTime > se2.StartTime

类似的逻辑可以用来在“时刻表数据”中找到其他东西，比如从A经B到C的一对火车。