如何理解SQL中的自连接？-轻识

点击关注上方“SQL数据库开发”，

设为“置顶或星标”，第一时间送达干货

SQL专栏

SQL基础知识第二版
SQL高级知识第二版

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

疯狂暗示：详解SQL中连续N天都出现的问题

自连接是什么

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

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

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

示例表内容

有如下一张表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里面的重复行。

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


推荐阅读
悟空无姓无名的时候，阎王生死簿是怎么写的呢？
5号发工资和25号发工资，还能看出公司是否靠谱？🤫
如果把14亿中国人都拉到一个微信群。。。
四款可视化工具，解决99%的可视化大屏需求
假如面试中大家都说实话....


后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群