MySQL - 查询以错误的顺序返回结果

Question

尽管两个查询的“ORDER BY”相同，但以下 MySQL 查询以不同的顺序返回结果:

表结构

+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| image_id        | int(10)     | NO   | PRI | NULL    | auto_increment |
| property_id     | int(10)     | NO   |     | 0       |                |
| image_title     | text        | NO   |     | NULL    |                |
| image_title_id  | int(10)     | NO   |     | 0       |                |
| image_index     | smallint(3) | NO   |     | 0       |                |
| image_version   | tinyint(1)  | NO   |     | 2       |                |
| image_landscape | tinyint(1)  | NO   |     | 1       |                |
| image_visible   | tinyint(1)  | NO   |     | 1       |                |
| image_type      | tinyint(1)  | NO   |     | 3       |                |
+-----------------+-------------+------+-----+---------+----------------+

测试 1

查询:

SELECT image_id, room_text
FROM property_record_images
INNER JOIN property_data_rooms ON property_record_images.image_title_id = property_data_rooms.room_id
WHERE property_id = 1029
ORDER BY image_index

结果:

+----------+-----------------+
| image_id | room_text       |
+----------+-----------------+
|     2042 | Front elevation |
|     2043 | Garden to rear  |
|     2044 | Kitchen         |
|     2045 | Breakfast area  |
|     2046 | Lounge          |
|     2047 | Master bedroom  |
|     2048 | Studio          |
+----------+-----------------+

测试 2

查询:

SELECT GROUP_CONCAT(CONCAT(property_record_images.image_id) SEPARATOR '|')
FROM property_record_images
INNER JOIN property ON property_record_images.property_id = property.property_id
WHERE property_record_images.property_id = 1029
ORDER BY image_index

结果:

+---------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT(property_record_images.image_id) SEPARATOR '|') |
+---------------------------------------------------------------------+
| 2048|2047|2044|2045|2046|2043|2042                                  |
+---------------------------------------------------------------------+

这是随机记录(不同的“property_id”)发生的，因此它不仅仅是反转第二个查询的 ORDER BY 那么简单。

知道为什么会发生这种情况以及我的查询出了问题吗？

Answer 1

参见http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

我认为你应该通过以下方式获得有序的组连接:

SELECT GROUP_CONCAT(CONCAT(property_record_images.image_id) ORDER BY image_index SEPARATOR '|')
FROM property_record_images
INNER JOIN property ON property_record_images.property_id = property.property_id
WHERE property_record_images.property_id = 1029
ORDER BY image_index