mysql - 用mysql统计第一名的人数

Question

好的，所以我有以下数据库:

CREATE TABLE IF NOT EXISTS `highscores` (
  `lid` int(11) NOT NULL,
  `username` varchar(15) NOT NULL,
  `score` int(16) NOT NULL,
  PRIMARY KEY  (`lid`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

lid 是级别 ID。

假设我在表中有以下值:

lid, username,score

1,sam,15 
1,joe,12
1,sue,6
1,josh,9
2,sam,8
2,joe,16 
2,sue,4
3,sam,65 
4,josh,87 
4,sue,43
5,sam,12
5,sue,28
5,joe,29
and so on.

我将如何创建一个查询(或者如果需要一组查询)来获得以下内容

sam has 3 high scores
joe has 2 high scores
josh has 1 high score 

提前致谢。

Answer 1

我还没有测试过，但是试试下面的查询

select 
    concat(h.username ," has ", count(h.username)," high scores ")
from
    highscores h inner join
    (select lid, max(score) as maxscore
     from highscores group by lid) t on h.lid = t.lid and h.score = t.maxscore
group by h.username