找到你要的答案

Q:Select sum of top three scores for each user

Q:为每个用户选择前三名的总和

I am having trouble writing a query for the following problem. I have tried some existing queries but cannot get the results I need.

I have a results table like this:

userid  score timestamp
1       50    5000
1       100   5000 
1       400   5000 
1       500   5000 
2       100   5000 
3       1000  4000 

The expected output of the query is like this:

userid  score
3       1000
1       1000
2       100

I want to select a top list where I have n best scores summed for each user and if there is a draw the user with the lowest timestamp is highest. I really tried to look at all old posts but could not find one that helped me.

Here is what I have tried:

SELECT sum(score) FROM (
   SELECT score 
   FROM results 
   WHERE userid=1 ORDER BY score DESC LIMIT 3
) as subquery

This gives me the results for one user, but I would like to have one query that fetches all in order.

我很难为以下问题写一个查询。我已经尝试了一些现有的查询,但不能得到我需要的结果。

我有这样的结果表:

userid  score timestamp
1       50    5000
1       100   5000 
1       400   5000 
1       500   5000 
2       100   5000 
3       1000  4000 

查询的预期输出是这样的:

userid  score
3       1000
1       1000
2       100

我想选择一个排行榜,我有最好的成绩相加,为每一个用户,如果有一个吸引用户以最低的时间戳是最高的。我真的想看看所有的旧帖子,但找不到一个帮助我。

这是我尝试过的:

SELECT sum(score) FROM (
   SELECT score 
   FROM results 
   WHERE userid=1 ORDER BY score DESC LIMIT 3
) as subquery

这给了我一个用户的结果,但我想有一个查询,以获取所有。

answer1: 回答1:

This is a pretty typical greatest-n-per-group problem. When I see those, I usually use a correlated subquery like this:

SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mT
  WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3;

This is not the whole solution, as it only gives you the top three scores for each user in its own row. To get the total, you can use SUM() wrapped around that subquery like this:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId;

Here is an SQL Fiddle example.

EDIT

Regarding the ordering (which I forgot the first time through), you can just order by totalScore in descending order, and then by MIN(timestamp) in ascending order so that users with the lowest timestamp appears first in the list. Here is the updated query:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

and here is an updated Fiddle link.

EDIT 2

As JPW pointed out in the comments, this query will not work if the user has the same score for multiple questions. To settle this, you can add an additional condition inside the subquery to order the users three rows by timestamp as well, like this:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score 
      AND mT.timeCol <= m.timeCol) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

I am still working on a solution to find out how to handle the scenario where the userid, score, and timestamp are all the same. In that case, you will have to find another tiebreaker. Perhaps you have a primary key column, and you can choose to take a higher/lower primary key?

这是一个很典型的greatest-n-per-group问题。当我看到这些,我通常使用相关子查询这样:

SELECT *
FROM myTable m
WHERE(
  SELECT COUNT(*)
  FROM myTable mT
  WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3;

这不是整个解决方案,因为它只给你自己的排在每一个用户的前三名。把总,您可以使用子查询sum()缠,像这样:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId;

这是一个SQL提琴的例子。

编辑

对于排序(我忘了第一次通过),你可以按总分降序排列,然后按分钟(时间戳)的顺序让用户以最低的时间出现在列表的第一。这里是更新的查询:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

这里是一个更新的小提琴链接。

编辑 2

JPW在评论中指出,该查询将不如果用户有多个问题得分相同的工作。为了解决这个,你可以订购用户三行的时间也在里面添加一个额外的查询条件,这样:

SELECT userId, SUM(score) AS totalScore
FROM(
  SELECT userId, score, timeCol
  FROM myTable m
  WHERE(
    SELECT COUNT(*)
    FROM myTable mT
    WHERE mT.userId = m.userId AND mT.score >= m.score 
      AND mT.timeCol <= m.timeCol) <= 3) tmp
GROUP BY userId
ORDER BY totalScore DESC, MIN(timeCol) ASC;

我仍在致力于解决如何处理的情况下,用户标识、评分、和时间戳都是一样的。在这种情况下,你将不得不寻找另外的决胜局。也许你有一个主键列,你可以选择采取更高/更低的主键?

answer2: 回答2:

Query for selecting top three scores from table.

SELECT score FROM result GROUP BY id ORDER BY score DESC LIMIT 3;

查询从表格中选择三大分数。

SELECT score FROM result GROUP BY id ORDER BY score DESC LIMIT 3;

answer3: 回答3:

Can you please try this?

SELECT score FROM result GROUP BY id ORDER BY score DESC, timestamp ASC LIMIT 3;

if 2 users have same score then it will set order depends on time.

你能试试这个吗?

SELECT score FROM result GROUP BY id ORDER BY score DESC, timestamp ASC LIMIT 3;

如果2个用户有相同的分数,那么它将设置顺序取决于时间。

answer4: 回答4:

You can use a subquery

SELECT r.userid,
(  SELECT sum(r2.score)
   FROM results r2
   WHERE r2.userid = r.userid 
   ORDER BY score DESC 
   LIMIT 3
) as sub
FROM result r
GROUP BY r.userid
ORDER BY sub desc

你可以使用子查询

SELECT r.userid,
(  SELECT sum(r2.score)
   FROM results r2
   WHERE r2.userid = r.userid 
   ORDER BY score DESC 
   LIMIT 3
) as sub
FROM result r
GROUP BY r.userid
ORDER BY sub desc
answer5: 回答5:

You should do it like this

SELECT SUM(score) as total, min(timestamp) as first, userid FROM scores
GROUP BY userid
ORDER BY total DESC, first ASC

This is way more efficient than sub queries. If you want to extract more fields than userid, then you need to add them to the group by.

This will of cause not limit the number of scores pr user, which indeed seems to require a subquery to solve.

你应该这样做

SELECT SUM(score) as total, min(timestamp) as first, userid FROM scores
GROUP BY userid
ORDER BY total DESC, first ASC

这是比子查询更有效的方法。如果你想获得比用户更多的领域,那么你需要将它们添加到组。

这将导致不限制分数PR用户数量,这的确似乎需要一个查询来解决。

mysql  sql  greatest-n-per-group  result