亲宝软件园·资讯

展开

关于 MySQL 嵌套子查询中无法关联主表字段问题的解决方法

bananaplan 人气:0

今天在工作中写项目的时候,遇到了一个让我感到几乎无解的问题,在转换了思路后,想出了一个折中的解决方案,记录如下。

其实,问题的场景,非常简单:

就是需要查询出上图的数据,红框是从 项目产品表 中查询的2个字段,绿框是从与项目产品表关联的 文章表 中查询出的1个字段。我希望实现的效果是,获取到项目产品对应的文章提交人数,即该项目产品,有多少人提交了文章。看似很简单啊,于是我开始撸 SQL 语句了。

先写个雏形

既然在查询项目产品表的时候,希望多查询1列数据,而此列数据是从其他关联表获取的,所以基本实现方式,是使用子查询。

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;

获得结果如下:

这个 SQL 语句,查询出了项目产品所对应的文章数,下面基于它再做个优化调整,把查询到的文章数量 article_num 变为提交文章的用户数量 member_num。

再优化一下,意外发生了

现在不是直接从文章表中,获取文章数量了,而是需要先根据文章表中的用户ID进行分组,获得分组数据之后,再通过 count(*) 聚合函数,拿到用户数量。于是继续调整 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 字段找不到。这是一个嵌套的子查询,在嵌套的最内层的子查询中,关联外部表的字段,是无法关联的。虽然我没找根据,但通过报错信息,也能大致看出一二。而且,在 DataGrip 中,把鼠标放到 s.id 上面时,也会出现一个提示:

虽然这个提示,我也不甚明了,但是感觉上,好像就是在告诉我,你无法关联到外部表的字段。

好像无解了,转变思路,柳暗花明

上面的 SQL 语句,看起来是如此的完美,可是就是有问题、不成立,咋办?

突然,灵机一动,想到一个方案,姑且一试。既然在嵌套的最内层的子查询中,做 WHERE subject_id = s.id 与主表的字段关联行不通,那么,就不在内层的子查询中做关联,把它提到外层的子查询中去,不就行的通了嘛。于是,改造 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;

主要关注子查询这里的改造,我们可以把这里的子查询做个分解。

首先,可以把子查询看成这样:(SELECT count(*) FROM t WHERE t.subject_id = s.id) AS member_num,把它理解成从 t 表中查询与主表的项目产品有关的记录数量。

然后,我们再把 t 表看成 (SELECT subject_id, mg_userid FROM art_subject_article GROUP BY subject_id, mg_userid) t,代表从文章表中查询出每个产品对应的用户ID。

最后把2个子查询,整合起来,就实现了查询项目产品表中,每个产品所对应的提交了文章的用户数量。

有没有更好的解决方案

这个折中的方案,虽然可以解决我的问题,但是,我依然想知道,有没有更好的、更标准的最佳实践。

并且此方案,也有3点不足:

所以,我将我遇到的这个问题,和解决方案分享在此,希望能帮助到有缘人,同时,也期望各位大神能够不吝赐教,分享一下最佳实践。

后记

我沉下心来,真的去谷歌上找证据去了,还真被我找到了,你猜怎么着,此问题真的是,无解!!!

这是我搜索到的线索,其中 https://bugs.mysql.com/bug.php?id=28814 这里有个人遇到了与我一样的问题,并且在下面的评论回复中,有个人抛出了 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 列表中的派生表内也不能有任何关联字段”。直接就给想要这么做的小伙伴们判了死刑,还真TM无解。

既然这种写法不支持,那么有没有什么替代方案?答案在这里找到了:https://dba.stackexchange.com/questions/237181/nested-subquery-giving-eror-of-unknown-column

里面也提供了非常有价值的信息:

在 MySQL 8.0.14 版本中,优化了关联子查询不能用在 FROM 中的问题,从这个版本开始,可以使用了!!!撒花,庆祝。。。

然而悲催的是,大多数的小伙伴们,用的都是 5.6 或 5.7 的版本吧,那么这个问题的唯一解法就是:不要在 FROM 的子查询中,使用字段关联。。。

好了,都被我猜对了,我真是个天才。第一,此问题真的无解;第二,想要解决,真的只能用迂回的、折中的解决方案。

看起来,有的时候,自己就是自己的救世主,自己就是那个期盼的大神。。。

加载全部内容

相关教程
猜你喜欢
用户评论