如何理解SQL中的自连接？

SQL数据库开发 2020-05-14

2052

与点击上方SQL数据库开发，关注获取SQL视频教程

SQL专栏

SQL数据库基础知识汇总

SQL数据库高级知识汇总

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

疯狂暗示：SQL如何求解连续性的问题？

自连接是什么

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

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

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

示例表内容

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

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

SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1

得到的结果是这样的：

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

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

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

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

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

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

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

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

SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1WHERE s1.Sname=s2.Sname

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

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

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

SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1WHERE s1.Sname<>s2.Sname

结果如下：

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

SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1WHERE s1.Sname>s2.Sname

得到的结果如下：

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

自连接实战

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

示例表结构

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

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

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

SELECT IDENTITY(INT) ID,Sname,ScoreINTO Student_TmpFROM Student

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

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

DELETE FROM Student_TmpWHERE 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 StudentTRUNCATE TABLE StudentINSERT INTO Student SELECT * FROM Student_TmpDROP TABLE Student_Tmp

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

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

——End——

后台回复关键字：1024，获取一份精心整理的技术干货

后台回复关键字：进群，带你进入高手如云的交流群。

推荐阅读

再见！程序员！！！
一成人网站泄露 108 亿数据，50 逾万中国用户信息被曝光...
SQL 查询优化之 WHERE 和 LIMIT 使用索引的奥秘
MySQL主从复制配置详解
神奇的 SQL，GROUP BY 真扎心，原来是这样！

这是一个能学到技术的公众号，欢迎关注

点击「阅读原文」了解SQL训练营

最后修改时间：2020-05-15 08:15:18

文章转载自SQL数据库开发，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

如何理解SQL中的自连接？

评论