mysql - 为什么在查询语句中添加 group by 后结果顺序发生了变化？

Question

数据库：MySQL 5.6

users桌子：

| id | name |
| -- | ---- |
| 1  | A    |
| 2  | B    |

user_likes桌子：

| id | user_id | star | created_at          |
| -- | ------- | ---- | ------------------- |
| 1  | 1       | 2    | 2018-09-12 00:00:00 |
| 2  | 2       | 3    | 2018-09-12 00:00:00 |

查询 1

select u.name, ul.user_id
from users u, user_likes ul
where u.id = ul.user_id
order by ul.star desc, ul.created_at desc;

查询 2

select u.name, ul.user_id
from users u, user_likes ul
where u.id = ul.user_id
group by ul.user_id
order by ul.star desc, ul.created_at desc;

为什么group by ul.user_id在这种情况下添加后结果顺序发生了变化？

那么想从中获取唯一的 user_id 记录user_likes，怎么办？

标签： mysqlsqldatabase

Answer 1

SELECT users.name , user_likes.user_id 
FROM user 
JOIN user_likes 
  ON user.id = user_likes.user_id  
 GROUP BY user.id  
order by user_likes.star desc, user_likes.created_at desc;

最佳方式结果