如何理解SQL中的自连接?

数据分析学习社
数据分析学习社
数据分析学习社
348
文章
0
评论
2021-05-2610:02:36 评论 205 1977字
摘要

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

自连接是什么
那我们如何理解自连接呢?
自连接说白了其实就是两张表结构和数据内容完全一样的表,在做数据处理的时候,我们通常会给它们分别重命名来加以区分(言外之意:不重命名也不行啊,不然数据库也不认识它们谁是谁),然后进行关联。
下面我们来看看它们到底是怎么进行自连接的
示例表内容
有如下一张表Student,表结构及数据如下:
如何理解SQL中的自连接?
当我们进行自连接时,不加任何过滤条件。具体如下:
SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1

 

得到的结果是这样的:
如何理解SQL中的自连接?
这结果看着好眼熟啊,好像在哪里见过。没错,其实就是我们数学上的排列。
大致的排列方式是酱紫的:
如何理解SQL中的自连接?
先是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

 

得到的结果就是两个自连接的表一一对应的了:
如何理解SQL中的自连接?
这里的就是自连接的精髓了,张三自己和自己进行了关联,所以你说这是什么连接?
但是我们工作中,使用自连接的目的并不是自己和自己关联,更多的时候是和表里的其他进行组合,像这样:
SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1WHERE s1.Sname<>s2.Sname

 

结果如下:
如何理解SQL中的自连接?
此外,如果我们想进一步的排除掉重复的数据行,比如张三,李四和李四,张三,我们默认这两行是重复数据(尽管他们顺序不同,但是在数学集合上,这两行可以看作是相同的结果集),只想保留一种的话,可以这样:
SELECT  s1.Sname AS Sname1,s2.Sname AS Sname2FROM Student s2,Student s1WHERE s1.Sname>s2.Sname

 

得到的结果如下:
如何理解SQL中的自连接?
这样我们就得到了"不重复"的3行数据了,这个与数学上的组合是一样的。
自连接实战
 
上面我们举了一个自连接来处理连续性问题,下面我们再举一个用自连接来删除重复数据的示例:
示例表结构
有如下一张Student表,表结构和数据如下:
如何理解SQL中的自连接?
我们想删除表中重复的数据行,该如何写这个SQL?
我们分析一下,发现这个表是没有主键ID的,为了区分它们的话,我需要给它新增一个虚列主键,怎么做?可以这样写:
SELECT IDENTITY(INTID,Sname,ScoreINTO Student_TmpFROM Student

 

这里我们使用自增长函数IDENTITY()来生成了一个生成一个类似自增主键的ID,并且将结果插入到Student_Tmp,其中Student_Tmp中的具体内容如下:
如何理解SQL中的自连接?
然后,我们可以通过保留最大值或最小值的方式来删除重复项,具体如下:
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中的自连接?

注意:由于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.

作者:平凡世界

转载如果涉及作品、版权其他问题请联系我们第一时间删除微信号:lovedata0520)

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: