今天在工作中寫(xiě)項(xiàng)目的時(shí)候，遇到了一個(gè)讓我感到幾乎無(wú)解的問(wèn)題，在轉(zhuǎn)換了思路后，想出了一個(gè)折中的解決方案，記錄如下。

其實(shí)，問(wèn)題的場(chǎng)景，非常簡(jiǎn)單：

就是需要查詢出上圖的數(shù)據(jù)，紅框是從 項(xiàng)目產(chǎn)品表 中查詢的2個(gè)字段，綠框是從與項(xiàng)目產(chǎn)品表關(guān)聯(lián)的 文章表 中查詢出的1個(gè)字段。我希望實(shí)現(xiàn)的效果是，獲取到項(xiàng)目產(chǎn)品對(duì)應(yīng)的文章提交人數(shù)，即該項(xiàng)目產(chǎn)品，有多少人提交了文章?？此坪芎?jiǎn)單啊，于是我開(kāi)始擼 SQL 語(yǔ)句了。

先寫(xiě)個(gè)雛形

既然在查詢項(xiàng)目產(chǎn)品表的時(shí)候，希望多查詢1列數(shù)據(jù)，而此列數(shù)據(jù)是從其他關(guān)聯(lián)表獲取的，所以基本實(shí)現(xiàn)方式，是使用子查詢。

SELECT s.id, s.name, (SELECT COUNT(*) FROM art_subject_article WHERE subject_id = s.id) AS article_num
FROM crm_subject s
ORDER BY article_num DESC;

獲得結(jié)果如下：

這個(gè) SQL 語(yǔ)句，查詢出了項(xiàng)目產(chǎn)品所對(duì)應(yīng)的文章數(shù)，下面基于它再做個(gè)優(yōu)化調(diào)整，把查詢到的文章數(shù)量 article_num 變?yōu)樘峤晃恼碌挠脩魯?shù)量 member_num。

再優(yōu)化一下，意外發(fā)生了

現(xiàn)在不是直接從文章表中，獲取文章數(shù)量了，而是需要先根據(jù)文章表中的用戶ID進(jìn)行分組，獲得分組數(shù)據(jù)之后，再通過(guò) count(*) 聚合函數(shù)，拿到用戶數(shù)量。于是繼續(xù)調(diào)整 SQL 如下：

SELECT s.id, s.name, (SELECT count(*) FROM (SELECT mg_userid FROM art_subject_article WHERE subject_id = s.id GROUP BY mg_userid) t) AS member_num
FROM crm_subject s
ORDER BY member_num DESC;

但是，運(yùn)行卻報(bào)錯(cuò)了：

報(bào)錯(cuò)信息說(shuō)：s.id 字段找不到。這是一個(gè)嵌套的子查詢，在嵌套的最內(nèi)層的子查詢中，關(guān)聯(lián)外部表的字段，是無(wú)法關(guān)聯(lián)的。雖然我沒(méi)找根據(jù)，但通過(guò)報(bào)錯(cuò)信息，也能大致看出一二。而且，在 DataGrip 中，把鼠標(biāo)放到 s.id 上面時(shí)，也會(huì)出現(xiàn)一個(gè)提示：

雖然這個(gè)提示，我也不甚明了，但是感覺(jué)上，好像就是在告訴我，你無(wú)法關(guān)聯(lián)到外部表的字段。

好像無(wú)解了，轉(zhuǎn)變思路，柳暗花明

上面的 SQL 語(yǔ)句，看起來(lái)是如此的完美，可是就是有問(wèn)題、不成立，咋辦？

突然，靈機(jī)一動(dòng)，想到一個(gè)方案，姑且一試。既然在嵌套的最內(nèi)層的子查詢中，做 WHERE subject_id = s.id 與主表的字段關(guān)聯(lián)行不通，那么，就不在內(nèi)層的子查詢中做關(guān)聯(lián)，把它提到外層的子查詢中去，不就行的通了嘛。于是，改造 SQL 如下：

SELECT s.id, s.name, (SELECT count(*) FROM (SELECT subject_id, mg_userid FROM art_subject_article GROUP BY subject_id, mg_userid) t WHERE t.subject_id = s.id) AS member_num
FROM crm_subject s
ORDER BY member_num DESC;

主要關(guān)注子查詢這里的改造，我們可以把這里的子查詢做個(gè)分解。

首先，可以把子查詢看成這樣：(SELECT count(*) FROM t WHERE t.subject_id = s.id) AS member_num，把它理解成從 t 表中查詢與主表的項(xiàng)目產(chǎn)品有關(guān)的記錄數(shù)量。

然后，我們?cè)侔?t 表看成 (SELECT subject_id, mg_userid FROM art_subject_article GROUP BY subject_id, mg_userid) t，代表從文章表中查詢出每個(gè)產(chǎn)品對(duì)應(yīng)的用戶ID。

最后把2個(gè)子查詢，整合起來(lái)，就實(shí)現(xiàn)了查詢項(xiàng)目產(chǎn)品表中，每個(gè)產(chǎn)品所對(duì)應(yīng)的提交了文章的用戶數(shù)量。

有沒(méi)有更好的解決方案

這個(gè)折中的方案，雖然可以解決我的問(wèn)題，但是，我依然想知道，有沒(méi)有更好的、更標(biāo)準(zhǔn)的最佳實(shí)踐。

并且此方案，也有3點(diǎn)不足：

• 改進(jìn)前我們是對(duì)文章表做項(xiàng)目產(chǎn)品關(guān)聯(lián)查詢后再分組，改進(jìn)后是對(duì)文章表做全表掃描后的分組，效率較低，在大數(shù)據(jù)下的表現(xiàn)不好。
• 優(yōu)化方案是基于兩層嵌套的子查詢進(jìn)行的，假如需要三層嵌套的子查詢，此方案估計(jì)又失效了。
• 此優(yōu)化方案較為局限，不具有普適性，不能很好的適用于各種業(yè)務(wù)場(chǎng)景。

所以，我將我遇到的這個(gè)問(wèn)題，和解決方案分享在此，希望能幫助到有緣人，同時(shí)，也期望各位大神能夠不吝賜教，分享一下最佳實(shí)踐。

后記

我沉下心來(lái)，真的去谷歌上找證據(jù)去了，還真被我找到了，你猜怎么著，此問(wèn)題真的是，無(wú)解?。。?/p>

這是我搜索到的線索，其中 https://bugs.mysql.com/bug.php?id=28814 這里有個(gè)人遇到了與我一樣的問(wèn)題，并且在下面的評(píng)論回復(fù)中，有個(gè)人拋出了 MySQL 的官方文檔，證實(shí)了此問(wèn)題的存在，不是 bug，而是 MySQL 本身就不支持。

這里引用官方文檔的說(shuō)明：

A correlated column can be present only in the subquery's WHERE clause (and not in the SELECT list, a JOIN or ORDER BY clause, a GROUP BY list, or a HAVING clause). Nor can there be any correlated column inside a derived table in the subquery's FROM list.

注意第二句話：“子查詢的 FROM 列表中的派生表內(nèi)也不能有任何關(guān)聯(lián)字段”。直接就給想要這么做的小伙伴們判了死刑，還真TM無(wú)解。

既然這種寫(xiě)法不支持，那么有沒(méi)有什么替代方案？答案在這里找到了：https://dba.stackexchange.com/questions/237181/nested-subquery-giving-eror-of-unknown-column。

里面也提供了非常有價(jià)值的信息：

在 MySQL 8.0.14 版本中，優(yōu)化了關(guān)聯(lián)子查詢不能用在 FROM 中的問(wèn)題，從這個(gè)版本開(kāi)始，可以使用了?。?！撒花，慶祝。。。

然而悲催的是，大多數(shù)的小伙伴們，用的都是 5.6 或 5.7 的版本吧，那么這個(gè)問(wèn)題的唯一解法就是：不要在 FROM 的子查詢中，使用字段關(guān)聯(lián)。。。

好了，都被我猜對(duì)了，我真是個(gè)天才。第一，此問(wèn)題真的無(wú)解；第二，想要解決，真的只能用迂回的、折中的解決方案。

看起來(lái)，有的時(shí)候，自己就是自己的救世主，自己就是那個(gè)期盼的大神。。。

到此這篇關(guān)于關(guān)于 MySQL 嵌套子查詢中，無(wú)法關(guān)聯(lián)主表字段問(wèn)題的折中解決方法的文章就介紹到這了,更多相關(guān)MySQL 嵌套子查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論