我在商店表中有商店列表，也有商店评论表，其中包含每个商店的多个注释。它们通过storeID外键链接。
这是较大查询的子查询，该查询将多条信息返回到按每个商店排序的一行中。此子查询的目的是将该商店的所有评论以yyyy-mm-dd-COMMENT-Name的格式返回到每个商店的单列中，并以最新评论在顶部排序。我可以使字符串正常工作，但是始终无法排序。
LEFT JOIN (SELECT group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' SEPARATOR '') as storecomments, StoreID from storecomment
inner join users on storecomment.CommentUserID = users.UserID
ORDER BY CommentDate DESC
group by StoreID)
as storecomments on store.StoreID = storecomments.StoreID
这通常可行，但是注释的排序失败，并且按照输入的顺序排列。
我也尝试过按字符串的第一部分进行排序，例如：
LEFT JOIN (SELECT group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' SEPARATOR '') as storecomments, StoreID from storecomment
inner join users on storecomment.CommentUserID = users.UserID
group by StoreID
Order by UNIX_TIMESTAMP(SUBSTRING(storecomments,9)) DESC)
as storecomments on store.StoreID = storecomments.StoreID
最后，我尝试将datetime转换为unix时间戳并以这种方式排序，但是仍然无法排序：
LEFT JOIN (SELECT group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' SEPARATOR '') as storecomments, StoreID from storecomment
inner join users on storecomment.CommentUserID = users.UserID
group by StoreID
Order by UNIX_TIMESTAMP(STR_TO_DATE(storecomment.CommentDate, '%Y-%m-%d %h:%i%p')) DESC
as storecomments on store.StoreID = storecomments.StoreID
我敢肯定有一个简单的方法可以解决这个问题，但是我看不到它。有没有人有什么建议？
最佳答案
您可以在group_concat中订购将值连接在一起的方式，因为它内置了对order by的支持。
更改您的group_concat看起来像这样：
group_concat(storecomment.CommentDate, ' - ', storecomment.Comment, ' - ', users.Name, '\n' ORDER BY storecomment.CommentDate DESC SEPARATOR '')
例：
mysql> create table example (user_id integer, group_id integer);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into example values (1, 1), (1, 2), (1, 3), (2, 7), (2, 4), (2, 5);
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select group_concat(group_id) from example group by user_id;
+------------------------+
| group_concat(group_id) |
+------------------------+
| 1,2,3 |
| 7,4,5 |
+------------------------+
2 rows in set (0.00 sec)
mysql> select group_concat(group_id order by group_id asc separator '-') from example group by user_id;
+------------------------------------------------------------+
| group_concat(group_id order by group_id asc separator '-') |
+------------------------------------------------------------+
| 1-2-3 |
| 4-5-7 |
+------------------------------------------------------------+
2 rows in set (0.00 sec)