关于 MySQL 嵌套子查询中无法关联主表字段问题的解决方法
作者:bananaplan 发布时间:2024-01-18 23:05:28
今天在工作中写项目的时候,遇到了一个让我感到几乎无解的问题,在转换了思路后,想出了一个折中的解决方案,记录如下。
其实,问题的场景,非常简单:
就是需要查询出上图的数据,红框是从 项目产品表
中查询的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 的子查询中,使用字段关联。。。
来源:https://www.cnblogs.com/bananaplan/p/mysql-cannot-find-outer-table-column-in-nested-subquery.html
猜你喜欢
- 一、Python urllib 模块是什么urllib 模块是 Python 标准库,其价值在于抓取网络上的 URL 资源,入门爬
- 代码'''数据集:Mnist训练集数量:60000(实际使用:10000)测试集数量:10000(实际使用:1000
- 本文实例讲述了python实现矩阵乘法的方法。分享给大家供大家参考。具体实现方法如下:def matrixMul(A, B):
- 如何在Mac中配置Python虚拟环境1.安装virtualenvpip3 install virtualenv2.安装virtualenv
- input() 的用法Python3.x 中input() 函数接受一个标准输入数据,返回为 string 类型。python3将input
- 新建label与button,并设置位置(grid)import tkinter as tkroot = tk.Tk()label = tk
- 可以,具体方法如下::<% set fs=createobject("scripting.
- 检测剪刀石头布三种手势,通过摄像头输入,方法如下:选用合适颜色空间及阈值提取皮肤部分使用滤波腐蚀膨胀等方法去噪边缘检测寻用合适方法分类Ope
- 把今天的学习的opencv知识先记录一下!运行环境是:pycharm话不多说,献上代码再说:import cv2 # openc
- 1. 把数字转换成字符串,应用"" + 1,虽然看起来比较丑一点,但事实上这个效率是最高的,性能上来说:("&
- 在前面的章节中,我们讨论了Series的计算方法与Pandas的自动对齐功能。不光是Series,DataFrame也是支持运算的,而且还是
- #coding:utf8import reimport urllibdef getHTML(url):
- 【需求背景】有时候我们要对比两份配置文件是不是一样,或者比较两个文本是否异样,可以使用linux命令行工具diff a_file b_fil
- 记录一下安装win10+GeForce GTX1060+CUDA 9.0+cuDNN7.3+tensorflow-gpu 1.12.0+py
- 我就废话不多说了,直接上代码吧!# -*- coding: utf-8 -*-"""Created on Sa
- counter 是一种特殊的字典,主要方便用来计数,key 是要计数的 item,value 保存的是个数。from collections
- 上一讲完成了基本博客的配置和项目工程的生成。这次开始将博客一些基本的操作主要是数据库方面学习。1.设计博客数据库表结构博客最主要的功能就是展
- python装饰器就是用于扩展原函数功能的一种函数,这个函数特殊的地方就是它的返回值也是一个函数,使用Python装饰器的一个好处就是:在不
- 译序:这篇文章是可用性大师 Jakob Nielsen 在10年前总结的,到今天仍然受用。通过这个时间跨度,可以得出,可用性话题不是某个时代
- 前言哈喽!哈喽。栗子上线啦~要说什么游戏能够获得大家的喜爱?唯射击游戏莫属。此前大火手游的《刺激战场》当然现在是叫做《和平精英》啦,想当初我