时间戳

首页 > 解决方案 > MySQL 5.7：按左连接的一对多表排序

mysql - MySQL 5.7：按左连接的一对多表排序

问题描述

数据库

mysql> DESCRIBE filtercategories;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(16) | NO   |     | NULL    |                |
| position | tinyint(4)  | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE tags;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| slug    | varchar(64)  | NO   | UNI | NULL    |                |
| name    | varchar(128) | NO   |     | NULL    |                |
| color   | varchar(7)   | NO   |     | NULL    |                |
| visible | tinyint(1)   | NO   |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE filtercategories_tags;
+-------------------+------------+------+-----+---------+----------------+
| Field             | Type       | Null | Key | Default | Extra          |
+-------------------+------------+------+-----+---------+----------------+
| id                | int(11)    | NO   | PRI | NULL    | auto_increment |
| filtercategory_id | int(11)    | NO   | MUL | NULL    |                |
| tag_id            | int(11)    | NO   | MUL | NULL    |                |
| position          | tinyint(4) | NO   |     | NULL    |                |
+-------------------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

目标

要返回按位置filtercategories排序的列表，左连接标签也应按位置排序。

到目前为止，尝试像：

SELECT
  fc.*,
  GROUP_CONCAT(t.name) AS tagNames
FROM filtercategories fc
LEFT JOIN (filtercategories_tags fc_t, tags t)
ON (
  fc_t.filtercategory_id = fc.id AND
  t.id = fc_t.tag_id
)
GROUP BY fc.id
ORDER BY fc.position

问题是 MySQL 不允许使用ORDER BY fc_t.position：

ER_WRONG_FIELD_WITH_GROUP：ORDER BY 子句的表达式#1 不在 GROUP BY 子句中，并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列“foo_db.fc_t.position”；这与 sql_mode=only_full_group_by 不兼容

标签： mysqlsql

解决方案

左连接的标签也应该按位置排序。

然后你应该ORDER BY在 : 中使用一个子句GROUP_CONCAT()：

SELECT
  fc.*,
  GROUP_CONCAT(t.name ORDER BY fc_t.position) AS tagNames
FROM filtercategories fc
LEFT JOIN filtercategories_tags fc_t ON fc_t.filtercategory_id = fc.id
LEFT JOIN tags t ON t.id = fc_t.tag_id
GROUP BY fc.id
ORDER BY fc.position

我还修复了您的 JOIN 语法。请不要在一个查询中将显式 JOIN 与逗号连接混合。这很难理解和调试。

推荐阅读