开发者

How to SELECT DISTINCT of one field and other fields From INNER JOIN table

I use PHP and 开发者_如何转开发MySQL and I have 2 tables:

  • table score: have fields 'user_id' and 'score'
  • table user: have fields 'user_id' and 'user_name'

People play game then store user_id and score in table 'score' every time game was ended. There are many rows for a single user in table score.

What I want? I want to select top 10 players with distinct user_name to show on the list, so what is the correct sql? Code below is now my current sql, but it's not show result what I want.

  SELECT DISTINCT * FROM score as t1
  INNER JOIN user AS t2 ON t1.user_id=t2.user_id
  WHERE score>0
  ORDER BY t1.score DESC
  LIMIT 10

What is the mistake for this?


does this work? (not tested)

    select username, max(score) as maxscore 
from score, user
 where score.userid=user.userid and score>0 
group by username 
order by maxscore desc


The following sql will return the top 10 highest scoring players sorted by their best score (not a top 10 of scores, but a top 10 of players).

SELECT
    u.user_name,
    max(s.score)
FROM 
    score as s
    INNER JOIN [user] AS u
        ON s.user_id = u.user_id
WHERE
    score > 0
GROUP BY
    u.user_name
ORDER BY 
    2 DESC
LIMIT 10
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜