[[397477]]

本文轉(zhuǎn)載自微信公眾號「SQL數(shù)據(jù)庫開發(fā)」，作者丶平凡世界。轉(zhuǎn)載本文請聯(lián)系SQL數(shù)據(jù)庫開發(fā)公眾號。

說起自連接，想必小伙伴們都聽說過。在進行數(shù)據(jù)處理時經(jīng)常會使用到自連接，特別是像一些連續(xù)性的問題中使用的比較多。

自連接是什么

那我們?nèi)绾卫斫庾赃B接呢?

自連接說白了其實就是兩張表結(jié)構(gòu)和數(shù)據(jù)內(nèi)容完全一樣的表，在做數(shù)據(jù)處理的時候，我們通常會給它們分別重命名來加以區(qū)分(言外之意：不重命名也不行啊，不然數(shù)據(jù)庫也不認識它們誰是誰)，然后進行關聯(lián)。

下面我們來看看它們到底是怎么進行自連接的

示例表內(nèi)容

有如下一張表Student，表結(jié)構(gòu)及數(shù)據(jù)如下：

當我們進行自連接時，不加任何過濾條件。具體如下：

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

得到的結(jié)果是這樣的：

這結(jié)果看著好眼熟啊，好像在哪里見過。沒錯，其實就是我們數(shù)學上的排列。

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

先是name1中的張三分別與name2中的張三，李四，王五組合成前面3條記錄

然后name1中的李四分別與name2中的張三，李四，王五組合成中間3條記錄

最后name1中的王五分別與name2中的張三，李四，王五組合成最后3條記錄

這樣就得到了我們上面的結(jié)果了。

但是我們常見的自連接大多數(shù)其實是有條件的。不管什么條件，其實都是在上面的結(jié)果上進行過濾的。

比如我們想找到一一對應的數(shù)據(jù)，可以這樣寫：

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

得到的結(jié)果就是兩個自連接的表一一對應的了：

這里的就是自連接的精髓了，張三自己和自己進行了關聯(lián)，所以你說這是什么連接?

但是我們工作中，使用自連接的目的并不是自己和自己關聯(lián)，更多的時候是和表里的其他進行組合，像這樣：

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

結(jié)果如下：

此外，如果我們想進一步的排除掉重復的數(shù)據(jù)行，比如張三，李四和李四，張三，我們默認這兩行是重復數(shù)據(jù)(盡管他們順序不同，但是在數(shù)學集合上，這兩行可以看作是相同的結(jié)果集)，只想保留一種的話，可以這樣：

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

得到的結(jié)果如下：

這樣我們就得到了“不重復”的3行數(shù)據(jù)了，這個與數(shù)學上的組合是一樣的。

自連接實戰(zhàn)

上面我們舉了一個自連接來處理連續(xù)性問題，下面我們再舉一個用自連接來刪除重復數(shù)據(jù)的示例：

示例表結(jié)構(gòu)

有如下一張Student表，表結(jié)構(gòu)和數(shù)據(jù)如下：

我們想刪除表中重復的數(shù)據(jù)行，該如何寫這個SQL?

我們分析一下，發(fā)現(xiàn)這個表是沒有主鍵ID的，為了區(qū)分它們的話，我需要給它新增一個虛列主鍵，怎么做?可以這樣寫：

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

這里我們使用自增長函數(shù)IDENTITY()來生成了一個生成一個類似自增主鍵的ID，并且將結(jié)果插入到Student_Tmp，其中Student_Tmp中的具體內(nèi)容如下：

然后，我們可以通過保留最大值或最小值的方式來刪除重復項，具體如下：

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里的內(nèi)容如下：

注意：由于SQL Server的一些限制，我們對源表不能進行上述操作，為了給大家演示自連接的作用，做了一定的調(diào)整。

如果想在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里面的重復行。

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