[[397477]]

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

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

自連接是什么

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

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

下面我們來看看它們到底是怎么進(jìn)行自連接的

示例表內(nèi)容

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

當(dāng)我們進(jìn)行自連接時(shí)，不加任何過濾條件。具體如下：

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

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

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

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

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

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

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

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

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

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

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

得到的結(jié)果就是兩個(gè)自連接的表一一對(duì)應(yīng)的了：

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

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

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

結(jié)果如下：

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

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

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

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

自連接實(shí)戰(zhàn)

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

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

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

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

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

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

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

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

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的一些限制，我們對(duì)源表不能進(jìn)行上述操作，為了給大家演示自連接的作用，做了一定的調(diào)整。

如果想在SQL Server中刪除原表中的重復(fù)行，可以使用如下方法:

SELECT DISTINCT * INTO Student_Tmp FROM Student 
TRUNCATE TABLE Student 
INSERT INTO Student SELECT * FROM Student_Tmp 
DROP TABLE Student_Tmp 

通過上述的辦法，我們使用自連接的方式刪除了Student_Tmp里面的重復(fù)行。

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