mysql表关系混乱_MySQL多表查询疑问

2019年6月20日,时隔快一个月,时不时想想这个问题,终于发现错在哪里了。

正确的写法应该是:当查询中存在group by子句时,select列表(或是having子句)中只能存在分组函数,或是出现在group by子句中的字段。

虽然没有弄清楚不按照上述原则书写SQL的错误产生机制是怎样的。但是按照上述原则,修改了group子句中的字段,改成跟select子句中的一样,运行多次结果都是正常的。

最近学习MySQL,遇到一个很奇怪的问题,可能跟SQL执行顺序有关。暂且记下来,后续有空再研究。

背景:有两张表student和score。如下,注意两张表的学号不是包含关系,但存在交集。student表score表

需求:查询所有学生的学号、姓名、选课数、总成绩。

思路:先将student和score左联结,然后根据学号分组对成绩求和。

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

group by a.学号;

注意,以上结果漏掉了score表中学号为'0005'的学生记录。但是MySQL不支持全连接。可以再查询score表中的学号不在student表的记录,然后union all。

如下是学号在score中,但不在student中的记录。(使用右联结)

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(b.成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号;

最后将两个结果union all,期望得到想要的结果。但是结果如下:异常结果

可以看出,总成绩计算有错。这是疑问之一!

……

第二天再次进行分析,发现同样的SQL语句,执行结果符合期望。如下,期望的结果

最后发现,由于重启了电脑,MySQL服务重启。才得到了上述期望的结果。并且,再次执行上述SQL语句,得到的执行结果依然是存在‘总成绩=0’的现象。但是在命令窗口重启MySQL服务后,第一次执行上述SQL语句,总能得到期望的结果。重启MySQL服务

于是转变思路使用left join(where is null)、right join(where is null)、inner join结合union all来实现。这样做得到了期望的结果。(反复执行如下SQL语句都没有问题)

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

where b.学号 is null

group by a.学号

UNION ALL

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(b.成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号

UNION ALL

select a.学号,a.姓名,count(b.课程号) as 选课数, sum(b.成绩) as 总成绩

from student as a INNER JOIN score as b

on a.学号=b.学号

GROUP BY a.学号;期望的结果,反复执行都正常

接着,又发现了类似的问题。

在单独使用内连接inner join的时候,使用‘group by a.学号’和‘group by b.学号’的效果是一样的,反复执行都没有问题。如下:group by a.学号group by b.学号

但是,使用left join(where is null)、right join(where is null)、inner join结合union all来实现的时候。如果inner join的SQL中使用‘group by b.学号’,在MySQL服务启动后第一次执行下列SQL可以得到期望结果,但是后续重复执行相同SQL的结果是异常的。MySQL服务启动后第一次执行MySQL服务启动后第2/3/4……次执行相同SQL,结果异常

刚入门的小白真是挺无奈的,这种问题不太好搜索。请教别人,答复说我的思维很混乱,按网上的标准答案实现需求即可。

先把这个疑问记录下来,后续有时间再分析或者找大神请教。

继续按计划学习……

错误原因:selelct子句中的字段没有保持与group by中的一致。修改如下:

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号,a.姓名

union ALL

select b.学号,a.姓名,count(b.课程号) as 选课数,sum(成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

group by b.学号,a.姓名;

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页
评论

打赏作者

隅隅隅

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值