在这个MYSQL查询中发生了什么,左边的连接和一个分组(在错误的列上)？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在这个MYSQL查询中发生了什么,左边的连接和一个分组(在错误的列上)？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6090字，纯文字阅读大概需要9分钟。

内容图文

我有以下表格：

create temporary table Items (item_id int, item_name varchar(10));
create temporary table ItemRating (item_id int, rating int);

有以下数据：

insert into Items (item_id, item_name) values (1,'Item 1'),(2,'Item 2'),(3,'Item 3'),(4,'Item 4'),(5,'Item 5');
insert into ItemRating values (1,9),(1,6),(3,10);

然后我运行以下查询：

select i.item_id, i.item_name, avg(ir.rating) from Items i left join ItemRating ir ON ir.item_id = i.item_id group by ir.item_id;

这是我得到的结果：

+---------+-----------+----------------+
| item_id | item_name | avg(ir.rating) |
+---------+-----------+----------------+
|       2 | Item 2    |           NULL |
|       1 | Item 1    |         7.5000 |
|       3 | Item 3    |        10.0000 |
+---------+-----------+----------------+

现在,我完全理解查询写错了,我想要的是在i.item_id上做一个组.但我不明白这种行为.为什么MYSQL在结果中显示item_id 2,而不是4或5？我实际上只希望看到第1项和第1项. 3因为它们是唯一在ItemRating中具有相应记录的人.

那么,任何人都可以向我解释一下MYSQL在做什么吗？

解决方法:

这是正在发生的事情.逐个考虑查询以及MySQL正在处理的内容.

首先,您要从项目中进行选择(从项目i中选择i.item_id,i.item_name,avg(ir.rating))：

+---------+-----------+
| item_id | item_name |
+---------+-----------+
|       1 | Item 1    |
|       2 | Item 2    |
|       3 | Item 3    |
|       4 | Item 4    |
|       5 | Item 5    |
+---------+-----------+

然后你将加入评级(左连接ItemRating ir ON ON.item_id = i.item_id).请注意,项目1在连接后出现在两行中,因为这是JOIN的定义方式 – 它为每个连接条件匹配返回一行(而LEFT基本上意味着“即使第一个表中的每一行都返回一次,即使该行没有连接条件匹配“).

+---------+-----------+-----------+------------+
| item_id | item_name | ir.rating | ir.item_id |
+---------+-----------+-----------+------------+
|       1 | Item 1    |         9 |          1 |
|       1 | Item 1    |         6 |          1 |
|       2 | Item 2    |      NULL |       NULL |
|       3 | Item 3    |        10 |          3 |
|       4 | Item 4    |      NULL |       NULL |
|       5 | Item 5    |      NULL |       NULL |
+---------+-----------+-----------+------------+

最后,您按分级进行分组(按ir.item_id分组).这将为每个唯一的ir.item_id返回一行.有三个唯一的ir.item_id(正如你在最后一列中看到的那样)：1,和NULL,以及3.对于每一个,它返回一行并平均评级.

所以,对于1,我们有：

+---------+-----------+-----------+------------+
| item_id | item_name | ir.rating | ir.item_id |
+---------+-----------+-----------+------------+
|       1 | Item 1    |         9 |          1 |
|       1 | Item 1    |         6 |          1 |
+---------+-----------+-----------+------------+

崩溃成：

+---------+-----------+----------------+------------+
| item_id | item_name | avg(ir.rating) | ir.item_id |
+---------+-----------+----------------+------------+
|       1 | Item 1    |            7.5 |          1 |
+---------+-----------+----------------+------------+

对于NULL,我们有：

+---------+-----------+-----------+------------+
| item_id | item_name | ir.rating | ir.item_id |
+---------+-----------+-----------+------------+
|       2 | Item 2    |      NULL |       NULL |
|       4 | Item 4    |      NULL |       NULL |
|       5 | Item 5    |      NULL |       NULL |
+---------+-----------+-----------+------------+

崩溃成：

+---------+-----------+----------------+------------+
| item_id | item_name | avg(ir.rating) | ir.item_id |
+---------+-----------+----------------+------------+
|        2| Item 2    |           NULL |       NULL |
+---------+-----------+----------------+------------+

对于3我们有：

+---------+-----------+-----------+------------+
| item_id | item_name | ir.rating | ir.item_id |
+---------+-----------+-----------+------------+
|       3 | Item 3    |        10 |          3 |
+---------+-----------+-----------+------------+

崩溃成：

+---------+-----------+----------------+------------+
| item_id | item_name | avg(ir.rating) | ir.item_id |
+---------+-----------+----------------+------------+
|       3 | Item 3    |             10 |          3 |
+---------+-----------+----------------+------------+

结合三个折叠结果给出：

+---------+-----------+----------------+------------+
| item_id | item_name | avg(ir.rating) | ir.item_id |
+---------+-----------+----------------+------------+
|       1 | Item 1    |            7.5 |          1 |
|       3 | Item 3    |             10 |          3 |
|       2 | Item 2    |           NULL |       NULL |
+---------+-----------+----------------+------------+

这是你得到的.

一个棘手的部分是NULL行折叠的方式.回想一下,这些是空行：

+---------+-----------+-----------+------------+
| item_id | item_name | ir.rating | ir.item_id |
+---------+-----------+-----------+------------+
|       2 | Item 2    |      NULL |       NULL |
|       4 | Item 4    |      NULL |       NULL |
|       5 | Item 5    |      NULL |       NULL |
+---------+-----------+-----------+------------+

当您执行分组时,大多数数据库系统甚至不允许您选择不属于该组的列. MySQL是个例外.由于您只对ir.rating进行分组,因此这是唯一一个允许您选择的方法,因为没有明确的方法以非聚合方式折叠三行. MySQL所做的只是选择它遇到的第一个,并使用该行中的值作为折叠值.所以(2,4,5)=> (2)和(第2项,第4项,第5项)=>第2项和(NULL,NULL,NULL)=>空值.这就是为什么你只看到第2行(你实际上看到三个折叠的行看起来像第2行).

要真正看到这一点并将其推向主场,请考虑以下问题：

select group_concat(i.item_id), group_concat(i.item_name), avg(ir.rating) from Items i left join ItemRating ir ON ir.item_id = i.item_id group by ir.item_id;

这与原始查询类似,但所有三个选定列现在都具有组聚合函数.我正在使用GROUP_CONCAT,它只是连接字符串以形成折叠版本(除了MySQL之外,这在其他SQL系统中也是有效的).这返回了：

+-------------------------+---------------------------+----------------+
| group_concat(i.item_id) | group_concat(i.item_name) | avg(ir.rating) |
+-------------------------+---------------------------+----------------+
| 2,4,5                   | Item 2,Item 4,Item 5      |           NULL |
| 1,1                     | Item 1,Item 1             |         7.5000 |
| 3                       | Item 3                    |        10.0000 |
+-------------------------+---------------------------+----------------+

内容总结

以上是互联网集市为您收集整理的在这个MYSQL查询中发生了什么,左边的连接和一个分组(在错误的列上)？全部内容，希望文章能够帮你解决在这个MYSQL查询中发生了什么,左边的连接和一个分组(在错误的列上)？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端