如何理解SQL中的何理自连接？

作者：丶平凡世界 2021-05-06 09:18:18数据库 MySQL 说起自连接，想必小伙伴们都听说过。解S接在进行数据处理时经常会使用到自连接，中的自连特别是何理像一些连续性的问题中使用的比较多。

[[397477]]

本文转载自微信公众号「SQL数据库开发」，解S接作者丶平凡世界。中的自连转载本文请联系SQL数据库开发公众号。何理

说起自连接，解S接想必小伙伴们都听说过。中的自连在进行数据处理时经常会使用到自连接，何理特别是解S接像一些连续性的问题中使用的比较多。

自连接是中的自连什么

那我们如何理解自连接呢?

自连接说白了其实就是两张表结构和数据内容完全一样的表，在做数据处理的何理时候，我们通常会给它们分别重命名来加以区分(言外之意：不重命名也不行啊，解S接不然数据库也不认识它们谁是中的自连谁)，然后进行关联。

下面我们来看看它们到底是怎么进行自连接的

示例表内容

有如下一张表Student，表结构及数据如下：

当我们进行自连接时，不加任何过滤条件。具体如下：

SELECT   
s1.Sname AS Sname1, 
s2.Sname AS Sname2 
FROM Student s2,Student s1 

得到的结果是这样的：

这结果看着好眼熟啊，好像在哪里见过。没错，其实就是我们数学上的排列。

大致的排列方式是酱紫的：

先是name1中的张三分别与name2中的张三，李四，王五组合成前面3条记录

然后name1中的李四分别与name2中的张三，李四，王五组合成中间3条记录

最后name1中的王五分别与name2中的张三，李四，王五组合成最后3条记录

这样就得到了我们上面的结果了。

但是我们常见的自连接大多数其实是有条件的。不管什么条件，其实都是在上面的结果上进行过滤的。

比如我们想找到一一对应的数据，可以这样写：

SELECT   
s1.Sname AS Sname1, 
s2.Sname AS Sname2 
FROM Student s2,Student s1 
WHERE s1.Sname=s2.Sname 

得到的结果就是两个自连接的表一一对应的了：

这里的就是自连接的精髓了，张三自己和自己进行了关联，所以你说这是什么连接?

但是我们工作中，使用自连接的目的并不是自己和自己关联，更多的时候是和表里的其他进行组合，像这样：

SELECT   
s1.Sname AS Sname1, 
s2.Sname AS Sname2 
FROM Student s2,Student s1 
WHERE s1.Sname<>s2.Sname 

结果如下：

此外，如果我们想进一步的排除掉重复的数据行，比如张三，李四和李四，张三，我们默认这两行是重复数据(尽管他们顺序不同，但是在数学集合上，这两行可以看作是相同的结果集)，只想保留一种的话，可以这样：

SELECT   
s1.Sname AS Sname1, 
s2.Sname AS Sname2 
FROM Student s2,Student s1 
WHERE s1.Sname>s2.Sname 

得到的结果如下：

这样我们就得到了“不重复”的3行数据了，这个与数学上的组合是一样的。

自连接实战

上面我们举了一个自连接来处理连续性问题，下面我们再举一个用自连接来删除重复数据的示例：

示例表结构

有如下一张Student表，表结构和数据如下：

我们想删除表中重复的数据行，该如何写这个SQL?

我们分析一下，发现这个表是没有主键ID的，为了区分它们的话，我需要给它新增一个虚列主键，怎么做?可以这样写：

SELECT  
IDENTITY(INT) ID, 
Sname, 
Score 
INTO Student_Tmp 
FROM Student 

这里我们使用自增长函数IDENTITY()来生成了一个生成一个类似自增主键的ID，并且将结果插入到Student_Tmp，其中Student_Tmp中的具体内容如下：

然后，我们可以通过保留最大值或最小值的方式来删除重复项，具体如下：

DELETE FROM Student_Tmp 
WHERE Student_Tmp.ID< ( 
            SELECT Max(s2.ID) 
            FROM Student_Tmp s2 
            WHERE Student_Tmp.Sname=s2.Sname 
            AND Student_Tmp.Score=s2.Score 
); 

这样我们就可以删除ID为3和4的列了，查询一下Student_Tmp里的内容如下：

注意：由于SQL Server的一些限制，我们对源表不能进行上述操作，为了给大家演示自连接的作用，做了一定的调整。

如果想在SQL Server中删除原表中的重复行，可以使用如下方法:

SELECT DISTINCT * INTO Student_Tmp FROM Student 
TRUNCATE TABLE Student 
INSERT INTO Student SELECT * FROM Student_Tmp 
DROP TABLE Student_Tmp 

通过上述的办法，我们使用自连接的方式删除了Student_Tmp里面的重复行。

以上就是自连接的一些主要用法，有不明白的地方欢迎给我留言~

 

责任编辑：武晓燕 来源： SQL数据库开发 SQL自连接数据

(责任编辑：焦点)