自连接是什么

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

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

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

示例表内容

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