CDA數(shù)據(jù)分析師 出品

導(dǎo)讀：本文主要介紹SQL環(huán)境下的關(guān)聯(lián)子查詢，如何理解關(guān)聯(lián)子查詢，以及如何使用關(guān)聯(lián)子查詢解決組內(nèi)篩選的問題。

什么是關(guān)聯(lián)子查詢

關(guān)聯(lián)子查詢是指和外部查詢有關(guān)聯(lián)的子查詢，具體來說就是在這個子查詢里使用了外部查詢包含的列。

因為這種可以使用關(guān)聯(lián)列的靈活性，將SQL查詢寫成子查詢的形式往往可以極大的簡化SQL語句，也使得SQL查詢語句更方便理解。

關(guān)聯(lián)子查詢的執(zhí)行邏輯

在關(guān)聯(lián)子查詢中，對于外部查詢返回的每一行數(shù)據(jù)，內(nèi)部查詢都要執(zhí)行一次。另外，在關(guān)聯(lián)子查詢中是信息流是雙向的。外部查詢的每行數(shù)據(jù)傳遞一個值給子查詢，然后子查詢?yōu)槊恳恍袛?shù)據(jù)執(zhí)行一次并返回它的記錄。然后，外部查詢根據(jù)返回的記錄做出決策。

關(guān)聯(lián)子查詢主要分為三步進行處理：

1、外部查詢得到一條記錄并傳遞到內(nèi)部查詢中；

2、內(nèi)部查詢基于輸入值執(zhí)行，并將返回值傳遞到外部查詢中；

3、外部查詢基于這個返回值再進行查詢，并做出決策。

關(guān)聯(lián)子查詢與普通子查詢的區(qū)別

在普通子查詢中，執(zhí)行順序是由內(nèi)到外，先執(zhí)行內(nèi)部查詢再執(zhí)行外部查詢。內(nèi)部查詢的執(zhí)行不依賴于外部查詢，且內(nèi)部查詢只處理一次，外部查詢基于內(nèi)部查詢返回值再進行查詢，就查詢完畢了。

而在關(guān)聯(lián)子查詢中，信息傳播是雙向而不是單向的。內(nèi)部查詢利用關(guān)聯(lián)子查詢涉及外部查詢提供的信息，外部查詢也會根據(jù)內(nèi)部查詢返回的記錄進行決策。內(nèi)部查詢的執(zhí)行依賴于外部查詢，不能單獨執(zhí)行。

應(yīng)用場景

在細分的組內(nèi)進行比較時，需要使用關(guān)聯(lián)子查詢。

比如查詢?nèi)T課程分數(shù)相同的學(xué)生，需要將各科考試成績的記錄按照學(xué)生進行分組，同一個學(xué)生的三科成績分為一組，對組內(nèi)的三科成績進行比較是否相同，來篩選滿足條件的學(xué)生。

再比如查詢價格低于該品類平均價格的商品，需要將各品類的商品信息按照品類進行分組，同一個品類的商品記錄分為一個組，對組內(nèi)的多個商品計算平均價格，來篩選滿足條件的商品。

例題精講

員工表的表結(jié)構(gòu)如下：

表中數(shù)據(jù)如下：

要解決的問題：

查詢工資高于同職位的平均工資的員工信息

普通子查詢的做法

遇到此類問題，首先想到的思路是對職位分組，這樣就能分別得到各個職位的平均工資，再比較每個員工的工資與其對應(yīng)職位的平均工資，大于則被篩選出來。

因此，第一步：分組統(tǒng)計各職位的平均工資

第二步：比較每個員工的工資與其對應(yīng)職位的平均工資

因為子查詢返回結(jié)果是5行，因此這段代碼根本無法執(zhí)行。

關(guān)聯(lián)子查詢的做法

通過設(shè)置表別名的方法，將一個表虛擬成兩個表進行自連接，并且使用關(guān)聯(lián)子查詢，內(nèi)部查詢返回的結(jié)果，傳遞給外部查詢進行比較篩選。

這段代碼的執(zhí)行步驟如下：

第一步：先執(zhí)行外部查詢，select* from emp e也就是遍歷表中的每一條記錄，而因為子查詢中用到了自連接（where job=e.job），所以將外部查詢的第一條記錄，也就是

傳遞給子查詢。

第二步：進入子查詢后，傳遞給子查詢的這條記錄的job是clerk，子查詢執(zhí)行select avg(sal) from empwhere job=e.job 時，就會篩選出所有job='clerk'的員工，計算出平均工資。相當(dāng)于執(zhí)行了

將這個計算值傳遞給外部查詢。

第三步：外部查詢基于1037.5進行篩選，找出同職位工資高于1037.5的員工。相當(dāng)于執(zhí)行了

循環(huán)執(zhí)行：

第一步：執(zhí)行外部查詢，即select* from emp e將外部查詢的第二條記錄，也就是

傳遞給子查詢。

第二步：進入子查詢后，傳遞給子查詢的這條記錄的job是salesman，子查詢執(zhí)行select avg(sal) from empwhere job=e.job時，就會篩選出所有job='salesman'的員工，計算出平均工資。相當(dāng)于執(zhí)行了

將這個計算值傳遞給外部查詢。

第三步：外部查詢基于1400進行篩選，找出同職位工資高于1400的員工。相當(dāng)于執(zhí)行了

繼續(xù)循環(huán)直到表中的最后一條記錄，最終返回滿足條件的員工信息。

總結(jié)

普通子查詢的內(nèi)部查詢獨立于外部查詢，可以單獨執(zhí)行，但子查詢僅執(zhí)行一次，外部查詢基于返回值再進行查詢和篩選，整個查詢過程就結(jié)束了。

在關(guān)聯(lián)子查詢中，內(nèi)部查詢依賴于外部查詢，不能單獨執(zhí)行。外部查詢執(zhí)行一次并傳遞一條記錄給子查詢，子查詢就要執(zhí)行一次并將返回值傳遞給外部查詢，外部查詢再執(zhí)行篩選并決策，如此循環(huán)直到表中最后一條記錄。