SQL 中的自连接 |最好的例子解释

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《SQL 中的自连接 |最好的例子解释》，文章讲解的知识点主要包括，如果你对文章方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

什么是 sql 中的自连接？

sql 中的自联接是一种表与其自身联接的联接类型。当您想要比较同一表中的行或从同一数据集中检索相关数据时，它非常有用。自连接通常用于建模层次关系（如员工-经理结构）或查找集合内的组合（如团队之间可能的匹配）。

定义：

自连接是一种常规连接，其中表使用不同的别名与其自身连接。它本质上用于将表的行与同一表中的其他行进行比较。

语法：

select a.column1, b.column2
from table_name a
join table_name b on a.common_column = b.common_column;

说明：

• table_name a：为表创建别名 (a)。
• table_name b：为同一个表创建另一个别名 (b)。
• on a.common_column = b.common_column：根据公共列连接两个别名的条件。

1.自加入示例：员工和经理场景

场景：

您有一个员工表，您需要找出哪个员工向哪个经理报告。表中的每一行都包含员工的详细信息，managerid 列保存经理的 employeeid。

示例表创建和数据插入：

-- create the employees table
create table employees (
    employeeid number primary key,
    employeename varchar2(50),
    managerid number
);

-- insert sample data
insert into employees (employeeid, employeename, managerid) 
values (1, 'john', null);
insert into employees (employeeid, employeename, managerid) 
values (2, 'mike', 1);
insert into employees (employeeid, employeename, managerid) 
values (3, 'sarah', 1);
insert into employees (employeeid, employeename, managerid) 
values (4, 'kate', 2);
insert into employees (employeeid, employeename, managerid) 
values (5, 'tom', 2);


-- commit the changes
commit;

oracle 中的自连接查询：

select e1.employeename as employee, 
       e2.employeename as manager
from employees e1
left join employees e2 on e1.managerid = e2.employeeid;

说明：

• e1 是代表员工的别名。
• e2 是代表管理者的另一个别名。

left join 有助于包含所有员工，甚至包括那些没有经理的员工（managerid 为 null）。

输出：

2.自加入示例：ipl 比赛（每个团队与其他团队比赛一次）

场景：

您有一个 ipl 球队列表，并且您想要生成一个比赛列表，其中每支球队都与其他球队交手一次。

示例表创建和数据插入：

-- create the teams table
create table teams (
    teamid number primary key,
    teamname varchar2(100)
);

-- insert sample data
insert into teams (teamid, teamname) 
values (1, 'mumbai indians');
insert into teams (teamid, teamname) 
values (2, 'chennai super kings');
insert into teams (teamid, teamname) 
values (3, 'royal challengers bangalore');
insert into teams (teamid, teamname) 
values (4, 'kolkata knight riders');

-- commit the changes
commit;

oracle 中的自连接查询：

select t1.teamname as team1, 
       t2.teamname as team2
from teams t1
join teams t2 on t1.teamid < t2.teamid;

说明：

• t1 和 t244 是 teams 表的别名。

条件 t1.teamid < t2.teamid 确保每个比赛配对仅列出一次（避免重复，例如 a 队对阵 b 队以及 b 队对阵 a 队）。

输出：

3.自加入示例：ipl 比赛（每支球队与其他球队对阵两次）

场景：

您想要生成一个列表，其中每个 ipl 球队都与其他球队进行两次比赛（一次作为主队，一次作为客队）。

oracle 中的自连接查询：

select t1.teamname as team1, 
       t2.teamname as team2
from teams t1
join teams t2 on t1.teamid != t2.teamid;

说明：

• t1 和 t2 是 teams 表的别名。

条件 t1.teamid != t2.teamid 确保列出所有可能的比赛，包括 a 队对阵 b 队以及 b 队对阵 a 队。

输出：

查找重复的客户记录 - 附加示例

场景：
您有一个客户表，其中每个客户都应具有名字、姓氏和出生日期的唯一组合。但是，可能会出现意外的重复，您希望使用自连接来识别它们。

示例表创建和数据插入：

-- create the customers table
create table customers (
    customerid number primary key,
    firstname varchar2(50),
    lastname varchar2(50),
    dateofbirth date
);

-- insert sample data (including duplicates)
insert into customers (customerid, firstname, lastname, dateofbirth) values (1, 'john', 'doe', to_date('1990-01-01', 'yyyy-mm-dd'));
insert into customers (customerid, firstname, lastname, dateofbirth) values (2, 'jane', 'smith', to_date('1992-02-02', 'yyyy-mm-dd'));
insert into customers (customerid, firstname, lastname, dateofbirth) values (3, 'john', 'doe', to_date('1990-01-01', 'yyyy-mm-dd'));
insert into customers (customerid, firstname, lastname, dateofbirth) values (4, 'alice', 'johnson', to_date('1995-03-03', 'yyyy-mm-dd'));
insert into customers (customerid, firstname, lastname, dateofbirth) values (5, 'john', 'doe', to_date('1990-01-01', 'yyyy-mm-dd'));

-- commit the changes
commit;

自连接查询查找重复项：

SELECT c1.CustomerID AS DuplicateRecordID1, 
       c2.CustomerID AS DuplicateRecordID2, 
       c1.FirstName, 
       c1.LastName, 
       c1.DateOfBirth
FROM Customers c1
JOIN Customers c2 ON c1.FirstName = c2.FirstName
                 AND c1.LastName = c2.LastName
                 AND c1.DateOfBirth = c2.DateOfBirth
                 AND c1.CustomerID < c2.CustomerID;

说明：

• c1 和 c2 是同一个 customers 表的别名。
• 条件 c1.firstname = c2.firstname and c1.lastname = c2.lastname and c1.dateofbirth = c2.dateofbirth 检查多个列中的匹配值，表明重复。
• c1.customerid < c2.customerid 确保每个重复对仅显示一次，避免像客户 a 与客户 b 以及客户 b 与客户 a 一样的重复。

输出：

结论：

• 自联接允许您通过创建多个别名来连接同一表中的行。它在需要在同一数据集中比较数据的场景很有用。在上面的例子中：
• 员工经理示例展示了如何对分层数据使用自连接。
• ipl 比赛说明了如何在单个数据集中生成组合，无论是每对单场比赛还是双场比赛（主客场比赛）。
• 这些场景展示了 sql 中自连接的灵活性和强大功能。

到这里，我们也就讲完了《SQL 中的自连接 |最好的例子解释》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于的知识点！