如何从 INNER JOIN 表中选择一个字段和其他字段的 DISTINCT

发布于 2024-12-08 18:17:43 字数 468 浏览 0 评论 0原文

我使用 PHP 和 MySQL，并且我有 2 个表：

表 Score：有字段“user_id”和“score”
表 user：有字段“user_id”和“user_name”

人们玩游戏，然后每次游戏结束时将 user_id 和分数存储在表“score”中。表分数中单个用户有许多行。

我想要什么？我想选择具有不同 user_name 的前 10 名玩家显示在列表中，那么正确的 sql 是什么？下面的代码现在是我当前的sql，但它没有显示我想要的结果。

  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

这有什么错误呢？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉世弥音 2024-12-15 18:17:43

这有用吗？（未测试）

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

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

回复收藏 0 原文

-小熊_ 2024-12-15 18:17:43

以下 sql 将返回按最佳成绩排序的前 10 名最高得分玩家（不是得分前 10 名，而是前 10 名玩家）。

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

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 原文

~没有更多了~