我有以下表格:

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 和 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    |
+---------+-----------+

然后您将加入评级(left join ItemRating ir ON ir.item_id = i.item_id)。请注意，Item 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 |
+---------+-----------+-----------+------------+

最后，您按评级分组(group by 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 是个异常(exception)。由于您仅在 ir.rating 上分组，因此这是唯一一个最能让您选择的选项，因为没有明确的方法以非聚合方式折叠三行。 MySQL 所做的只是选择它遇到的第一个并使用该行中的值作为折叠值。所以 (2,4,5) => (2) and (Item 2, Item 4, Item 5) => Item 2 and (NULL, 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 查询中发生了什么？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/8348658/