Mysql同时获得高分和所有提交的分数

Question

我有以下高分表:

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

并有以下查询:

1- 列出得分最高的用户

SELECT
   h.userid,h.username ,count(h.username)
FROM
    highscores h inner join
    (select lid, min(score) as minscore,COUNT( * ) AS totalScores
     FROM highscores GROUP BY lid) t on h.lid = t.lid and h.score = t.minscore
GROUP BY h.username ORDER BY `count(h.username)`  DESC

2-列出提交分数最多的用户

SELECT username, COUNT( * ) 
FROM highscores
GROUP BY username
ORDER BY COUNT( * ) DESC 

我想要的是一个查询，它可以同时获得用户的总高分和提交的分数，并按最高分的用户排序。

类似(不确定如何格式化表格)-

-用户名--highscores--submittedscores

-约翰--------65------------755

-吉尔------------42------------1435

Answer 1

select h.username, h.highScores, s.submittedScores
from
(
    SELECT
        h.username, count(h.username) as highScores
    FROM
        highscores h inner join
        (select lid, min(score) as minscore,COUNT( * ) AS totalScores
         FROM highscores GROUP BY lid) t on h.lid = t.lid and h.score = t.minscore
    GROUP BY h.username
) as h
inner join
(
    SELECT username, COUNT(*) as submittedScores
    FROM highscores
    GROUP BY username
) as s
    on s.username = h.username
order by h.highScores desc, s.submittedScores

我刚刚获取了您的两个结果，并将它们与内部连接结合起来。这不正是您所需要的吗？

我没有详述您的两个问题，所以很可能有一个更简单的版本。