MySQL从涉及的表中选择

I have two tables.
One table looks like: user(id, username)
The other Table looks like: rating(user_id, rating)

我有两张桌子。一个表看起来像:user(id,username)另一个表看起来像:rating(user_id,rating)

Now i want to select the find the top 10 rated users with:

现在我想选择找到排名前10位的用户:

SELECT *
FROM rating order by rating
LIMIT 10;

now i want to "switch" to the username and show the usernames from the top 10 ids.

现在我想“切换”到用户名并显示前10个ID中的用户名。

How I could do that?

我怎么能这样做?

3 个解决方案

#1

SELECT u.username,r.rating
FROM user u
INNER JOIN rating r ON u.id = r.user_id
ORDER BY r.rating DESC
LIMIT 10;

#2

SELECT u.username, r.rating
FROM rating as r, user as u 
WHERE r.user_id = u.id 
ORDER BY r.rating DESC 
LIMIT 10;

#3

try this query

试试这个查询

SELECT user.username, rating.rating FROM user LEFT JOIN rating ON user.id = rating.user_id ORDER BY rating.rating DESC LIMIT 10;

SELECT user.username,rating.rating FROM user LEFT JOIN rating ON user.id = rating.user_id ORDER BY rating.rating DESC LIMIT 10;