MySql LEFT OUTER JOIN 导致重复行

Question

我正在运行查询以获取前 10 个个人资料(将它们视为一篇文章，显示商店何时开业并包含有关该商店的信息)。我使用OUTER JOIN来选择*属于配置文件PK的图像。

我正在运行以下查询，我想要关注的主要部分是JOIN。我不会发布整个查询，因为它只是一大堆 'table'.'colname' = 'table.colname'。

但这就是我的外部连接期间神奇发生的地方。

LEFT JOIN `content_image` AS `image` ON `profile`.`content_ptr_id` = `image`.`content_id`

完整查询:

我已经采用了这样的格式，以便每个人都可以看到查询，而无需无休止地向右滚动。

select `profile`.`content_ptr_id` AS `profile.content_ptr_id`,
       `profile`.`body` AS `profile.body`,
       `profile`.`web_site` AS `profile.web_site`,
       `profile`.`email` AS `profile.email`,
       `profile`.`hours` AS `profile.hours`,
       `profile`.`price_range` AS `profile.price_range`,
       `profile`.`price_range_high` AS `profile.price_range_high`,
       `profile`.`primary_category_id` AS `profile.primary_category_id`,
       `profile`.`business_contact_email` AS `profile.business_contact_email`,
       `profile`.`business_contact_phone` AS `profile.business_contact_phone`,
       `profile`.`show_in_directory` AS `profile.show_in_directory`,
       `image`.`id` AS `image.id`,
       `image`.`content_id` AS `image.content_id`,
       `image`.`type` AS `image.type`,
       `image`.`order` AS `image.order`,
       `image`.`caption` AS `image.caption`,
       `image`.`author_id` AS `image.author_id`,
       `image`.`image` AS `image.image`,
       `image`.`link_url` AS `image.link_url`
FROM content_profile AS profile
LEFT JOIN `content_image` AS `image` ON `profile`.`content_ptr_id` = `image`.`content_id`
GROUP BY profile.content_ptr_id
LIMIT 10, 12

有没有办法可以根据个人资料对结果进行分组？例如，所有图像都会显示在一个配置文件结果中？我无法使用分组依据，因为我收到错误

Error: ER_WRONG_FIELD_WITH_GROUP: Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'broadsheet.image.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
  code: 'ER_WRONG_FIELD_WITH_GROUP',
  errno: 1055,
  sqlState: '42000',
  index: 0 }

是否有可能解决此 group by 错误或我可以运行的其他查询？

表格:

内容图像

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| content_id | int(11)      | NO   | MUL | NULL    |                |
| type       | varchar(255) | NO   |     | NULL    |                |
| order      | int(11)      | NO   |     | NULL    |                |
| caption    | longtext     | NO   |     | NULL    |                |
| author_id  | int(11)      | YES  | MUL | NULL    |                |
| image      | varchar(255) | YES  |     | NULL    |                |
| link_url   | varchar(200) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

内容配置文件

+------------------------+----------------------+------+-----+---------+-------+
| Field                  | Type                 | Null | Key | Default | Extra |
+------------------------+----------------------+------+-----+---------+-------+
| content_ptr_id         | int(11)              | NO   | PRI | NULL    |       |
| body                   | longtext             | NO   |     | NULL    |       |
| web_site               | varchar(200)         | NO   |     | NULL    |       |
| email                  | varchar(75)          | NO   |     | NULL    |       |
| menu                   | longtext             | NO   |     | NULL    |       |
| hours                  | longtext             | NO   |     | NULL    |       |
| price_range            | smallint(5) unsigned | YES  | MUL | NULL    |       |
| price_range_high       | smallint(5) unsigned | YES  |     | NULL    |       |
| primary_category_id    | int(11)              | NO   |     | NULL    |       |
| business_contact_name  | varchar(255)         | NO   |     | NULL    |       |
| business_contact_email | varchar(75)          | NO   |     | NULL    |       |
| business_contact_phone | varchar(20)          | NO   |     | NULL    |       |
| show_in_directory      | tinyint(1)           | NO   |     | NULL    |       |
+------------------------+----------------------+------+-----+---------+-------+

Answer 1

通过阅读您的问题，我认为您没有掌握 GROUP BY 子句的工作原理。

所以我的回答的简短摘要是:学习 GROUP BY 子句的基础知识。

我将仅使用少量列以使解释更容易。

查询的第一个问题是您没有正确使用 group by 子句 - 使用 group by 子句时，选择的所有列必须位于 group by 子句中或使用聚合函数选择。

假设这些是您选择的唯一列:profile.content_ptr_id配置文件.body个人资料.web_site图像.idimage.content_id

查询如下所示:

SELECT `profile.content_ptr_id`, `profile.body`, `profile.web_site`, `image.id`, `image.content_id`
FROM ...
GROUP BY `profile.content_ptr_id`

此查询将出错，因为您没有指定如何将 profile.body、profile.web_site、image 的多行合并为一行.id、image.content_id。数据库不知道您想要如何合并其他列，因为您可以分组或使用聚合函数，例如 min()、max()、count() 等。

因此，修复上述查询中引发的错误的一种解决方案如下:

SELECT `profile.content_ptr_id`, `profile.body`, `profile.web_site`, `image.id`, `image.content_id`
FROM ...
GROUP BY `profile.content_ptr_id`, `profile.body`, `profile.web_site`, `image.id`, `image.content_id`

在这里，我将所有列放入 group by 子句中，该子句对查询进行分组并选择 profile.content_ptr_id、profile.body、profile.web_site、image.id、image.content_id 列。

以下是一个示例查询，其中未包含 group by 子句中的所有列:

假设您想了解每个配置文件有多少张图像。您可以使用如下查询:

SELECT `profile.content_ptr_id`, `profile.body`, `profile.web_site`, COUNT(`image.id`)
FROM ...
GROUP BY `profile.content_ptr_id`, `profile.body`, `profile.web_site`

通过此查询，您可以了解 profile.content_ptr_id、profile.body、profile.web_site 的每个唯一组合有多少张图片> 栏目。

请注意，在我的前两个示例中，选择的所有列要么包含在 group by 子句中，要么使用聚合函数选择。这是使用group by子句时所有查询都需要遵循的规则，否则数据库将引发错误。

现在，让我们开始回答您的问题:

“有没有一种方法可以将每个个人资料的结果分组？例如，所有图像都将显示在一个个人资料结果中？”

我将使用以下模拟数据来解释:

profile
+----------------+--------------+---------------+
| content_ptr_id |     body     |   web_site    |
+----------------+--------------+---------------+
|      100       |     body1    |     web1      |
+----------------+--------------+---------------+

image
+--------+-------------+
|   id   |  content_id |
+--------+-------------+
|  iid1  |     100     |
|  iid2  |     100     |
+--------+-------------+

如果不进行连接，结果如下:

SELECT `profile.content_ptr_id`, `profile.body`, `profile.web_site`, `image.id`, `image.content_id`
FROM ...

+----------------+--------------+---------------+--------+-------------+
| content_ptr_id |     body     |   web_site    |   id   |  content_id |
+----------------+--------------+---------------+--------+-------------+
|      100       |     body1    |     web1      |  iid1  |     100     |
|      100       |     body1    |     web1      |  iid2  |     100     |
+----------------+--------------+---------------+--------+-------------+

通过按所有列分组，您无法实现按配置文件对结果进行分组(组合后每个配置文件仅显示一行)的目标，因为结果将是相同的:

SELECT `profile.content_ptr_id`, `profile.body`, `profile.web_site`, `image.id`, `image.content_id`
FROM ...
GROUP BY `profile.content_ptr_id`, `profile.body`, `profile.web_site`, `image.id`, `image.content_id`

将返回

+----------------+--------------+---------------+--------+-------------+
| content_ptr_id |     body     |   web_site    |   id   |  content_id |
+----------------+--------------+---------------+--------+-------------+
|      100       |     body1    |     web1      |  iid1  |     100     |
|      100       |     body1    |     web1      |  iid2  |     100     |
+----------------+--------------+---------------+--------+-------------+

您需要回答的问题是如何显示要组合的非唯一列 - 在本例中是 image.id。您可以使用 count，但这只会返回一个数字。如果要显示所有文本，可以使用 GROUP_CONCAT() ，默认情况下它将连接所有以逗号分隔的值。如果您使用 GROUP_CONCAT()，结果将如下所示:

SELECT `profile.content_ptr_id`, `profile.body`, `profile.web_site`, GROUP_CONCAT(`image.id`), GROUP_CONCAT(`image.content_id`)
FROM ...
GROUP BY `profile.content_ptr_id`, `profile.body`, `profile.web_site`

此查询将返回:

+----------------+--------------+---------------+--------------------+-------------+
| content_ptr_id |     body     |   web_site    |  GROUP_CONCAT(id)  |  content_id |
+----------------+--------------+---------------+--------------------+-------------+
|      100       |     body1    |     web1      |     iid1,iid2      |     100     |
+----------------+--------------+---------------+--------------------+-------------+

如果您想对所有图像列使用 GROUP_CONCAT()，那么请继续，但对合并许多行的许多列执行此操作可能会降低表的可读性。但无论哪种方式，我建议您阅读一些文章来熟悉 GROUP BY 子句的工作原理。