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

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

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

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

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

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

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

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

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

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

3、外部查詢基于這個(gè)返回值再進(jìn)行查詢，并做出決策。

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

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

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

應(yīng)用場(chǎng)景

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

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

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

例題精講

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

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

要解決的問(wèn)題：

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

普通子查詢的做法

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

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

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

因?yàn)樽硬樵兎祷亟Y(jié)果是5行，因此這段代碼根本無(wú)法執(zhí)行。

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

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

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

第一步：先執(zhí)行外部查詢，select* from emp e也就是遍歷表中的每一條記錄，而因?yàn)樽硬樵冎杏玫搅俗赃B接（where job=e.job），所以將外部查詢的第一條記錄，也就是

傳遞給子查詢。

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

將這個(gè)計(jì)算值傳遞給外部查詢。

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

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

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

傳遞給子查詢。

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

將這個(gè)計(jì)算值傳遞給外部查詢。

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

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

總結(jié)

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

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