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

其實，問題的場景，非常簡單：

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

先寫個雛形

既然在查詢項目產(chǎn)品表的時候，希望多查詢1列數(shù)據(jù)，而此列數(shù)據(jù)是從其他關(guān)聯(lián)表獲取的，所以基本實現(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é)果如下：

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

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

現(xiàn)在不是直接從文章表中，獲取文章數(shù)量了，而是需要先根據(jù)文章表中的用戶ID進行分組，獲得分組數(shù)據(jù)之后，再通過 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;

但是，運行卻報錯了：

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

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

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

上面的 SQL 語句，看起來是如此的完美，可是就是有問題、不成立，咋辦？

突然，靈機一動，想到一個方案，姑且一試。既然在嵌套的最內(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)注子查詢這里的改造，我們可以把這里的子查詢做個分解。

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

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

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

有沒有更好的解決方案

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

并且此方案，也有3點不足：

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

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

后記

我沉下心來，真的去谷歌上找證據(jù)去了，還真被我找到了，你猜怎么著，此問題真的是，無解?。?！

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

這里引用官方文檔的說明：

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無解。

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

里面也提供了非常有價值的信息：

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

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

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

看起來，有的時候，自己就是自己的救世主，自己就是那個期盼的大神。。。

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

最新評論