您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

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

时间:2022-12-24 10:59:01  来源:今日头条  作者:Java架构师修炼之道

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

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

 

就是需要查询出上图的数据,红框是从 项目产品表 中查询的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点不足:

  1. 改进前我们是对文章表做项目产品关联查询后再分组,改进后是对文章表做全表扫描后的分组,效率较低,在大数据下的表现不好。
  2. 优化方案是基于两层嵌套的子查询进行的,假如需要三层嵌套的子查询,此方案估计又失效了。
  3. 此优化方案较为局限,不具有普适性,不能很好的适用于各种业务场景。

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



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
今天在工作中写项目的时候,遇到了一个让我感到几乎无解的问题,在转换了思路后,想出了一个折中的解决方案,记录如下。其实,问题的场景,非常简单: 就是需要查询出上图的数据,红框是从...【详细内容】
2022-12-24  Tags: MySQL  点击:(0)  评论:(0)  加入收藏
最近,某同事在生产上遇到一个 mysql 死锁的问题,于是在帮忙解决问题后,特意花了一周的时间,把 MySQL 所有的锁都整理了一遍,今天就来一起聊聊 MySQL锁。 申明:本文基于 MySQL 8.0....【详细内容】
2022-12-18  Tags: MySQL  点击:(1)  评论:(0)  加入收藏
Insert Intention Lock,中文我们也称之为插入意向锁。这个可以算是对我们之前所讲的 Gap Lock 的一个补充,关于 Gap Lock,如果还有小伙伴不懂,可以参考:记录锁、间隙锁与 Next-Ke...【详细内容】
2022-12-18  Tags: MySQL  点击:(11)  评论:(0)  加入收藏
Adminer (以前叫phpMinAdmin),是一个用 PHP 编写的前端数据库管理工具。与 phpMyAdmin 不同的是,它只包含一个 PHP 文件,可以在安装 Adminer 的目标服务器上下载。与 phpMyAdmi...【详细内容】
2022-11-23  Tags: MySQL  点击:(37)  评论:(0)  加入收藏
表结构和初始数据 新建表结构CREATE TABLE `json_test` ( `id` int NOT NULL AUTO_INCREMENT, `roles` json DEFAULT NULL COMMENT '角色', `project` json DEFA...【详细内容】
2022-11-18  Tags: MySQL  点击:(141)  评论:(0)  加入收藏
pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新特定表里的时间戳,再与本地系统时间对比来得出其延迟。具体流程:1)在主库上创建一张heart...【详细内容】
2022-11-11  Tags: MySQL  点击:(38)  评论:(0)  加入收藏
今天我们来详细了解一下主从同步延迟时读写分离发生写后读不到的问题,依次讲解问题出现的原因,解决策略以及 Sharding-jdbc、MyCat 和 MaxScale 等开源数据库中间件具体的实现...【详细内容】
2022-11-09  Tags: MySQL  点击:(48)  评论:(0)  加入收藏
谈到索引失效,大家可能都能列举出几个场景,比如:后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景:索引成本分析。我先用一个具体的例子来描述一下这个场景...【详细内容】
2022-11-07  Tags: MySQL  点击:(43)  评论:(0)  加入收藏
满怀忧思不如先干再说!前言数据库拆分属于中高级开发要做的事情,不过具体的看企业吧。各种情况都会出现,这篇文章主要是基于Mycat去实现一下数据库拆分,至于拆分的思想,后边补一...【详细内容】
2022-11-01  Tags: MySQL  点击:(50)  评论:(0)  加入收藏
一. 前言这一篇来详细看看 EXPLAIN 各个参数的含义和扩展 , 整理出来便于使用时快速查询二 . explain 使用 三. 业务实践在日常实践中 , 我们应该如何使用 explain 提供的...【详细内容】
2022-10-17  Tags: MySQL  点击:(45)  评论:(0)  加入收藏
▌简易百科推荐
今天在工作中写项目的时候,遇到了一个让我感到几乎无解的问题,在转换了思路后,想出了一个折中的解决方案,记录如下。其实,问题的场景,非常简单: 就是需要查询出上图的数据,红框是从...【详细内容】
2022-12-24  Java架构师修炼之道  今日头条  Tags:MySQL   点击:(0)  评论:(0)  加入收藏
最近,某同事在生产上遇到一个 mysql 死锁的问题,于是在帮忙解决问题后,特意花了一周的时间,把 MySQL 所有的锁都整理了一遍,今天就来一起聊聊 MySQL锁。 申明:本文基于 MySQL 8.0....【详细内容】
2022-12-18  猿Java  知乎  Tags:MySQL   点击:(1)  评论:(0)  加入收藏
Insert Intention Lock,中文我们也称之为插入意向锁。这个可以算是对我们之前所讲的 Gap Lock 的一个补充,关于 Gap Lock,如果还有小伙伴不懂,可以参考:记录锁、间隙锁与 Next-Ke...【详细内容】
2022-12-18  江南一点雨  今日头条  Tags:MySQL   点击:(11)  评论:(0)  加入收藏
Adminer (以前叫phpMinAdmin),是一个用 PHP 编写的前端数据库管理工具。与 phpMyAdmin 不同的是,它只包含一个 PHP 文件,可以在安装 Adminer 的目标服务器上下载。与 phpMyAdmi...【详细内容】
2022-11-23  程序猿阿嘴  今日头条  Tags:MySQL   点击:(37)  评论:(0)  加入收藏
pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新特定表里的时间戳,再与本地系统时间对比来得出其延迟。具体流程:1)在主库上创建一张heart...【详细内容】
2022-11-11  mj谈云技术  今日头条  Tags:MySQL   点击:(38)  评论:(0)  加入收藏
今天我们来详细了解一下主从同步延迟时读写分离发生写后读不到的问题,依次讲解问题出现的原因,解决策略以及 Sharding-jdbc、MyCat 和 MaxScale 等开源数据库中间件具体的实现...【详细内容】
2022-11-09   ITPUB2022   网易号  Tags:MySQL   点击:(48)  评论:(0)  加入收藏
谈到索引失效,大家可能都能列举出几个场景,比如:后模糊查询、条件中带函数、索引中断等等。今天我想和你分享另一个场景:索引成本分析。我先用一个具体的例子来描述一下这个场景...【详细内容】
2022-11-07  杨同学technotes  今日头条  Tags:MySQL   点击:(43)  评论:(0)  加入收藏
最近发现之前使用的MyBatis插件很久都没更新了,就想换个其他插件来用用。偶然发现MyBatis-Plus团队也开发了一款插件MyBatisX,体验了一把确实非常好用,提示很全,而且还能通过GUI...【详细内容】
2022-11-01  老诚不bug  今日头条  Tags:MyBatis-Plus   点击:(46)  评论:(0)  加入收藏
满怀忧思不如先干再说!前言数据库拆分属于中高级开发要做的事情,不过具体的看企业吧。各种情况都会出现,这篇文章主要是基于Mycat去实现一下数据库拆分,至于拆分的思想,后边补一...【详细内容】
2022-11-01  添甄  今日头条  Tags:MySQL   点击:(50)  评论:(0)  加入收藏
一. 前言这一篇来详细看看 EXPLAIN 各个参数的含义和扩展 , 整理出来便于使用时快速查询二 . explain 使用 三. 业务实践在日常实践中 , 我们应该如何使用 explain 提供的...【详细内容】
2022-10-17  架构师之道  今日头条  Tags:explain   点击:(45)  评论:(0)  加入收藏
站内最新
站内热门
站内头条