选择对行有限制的MAX值 [英] Select MAX value with restriction to rows

问题描述

我有3张桌子:

比赛日:

matchday_id | season_id | userid | points | matchday
----------------------------------------------------
1           |  1        | 1      | 33     | 1
2           |  1        | 2      | 45     | 1
etc

玩家

userid | username
-----------------
1      | user1
2      | user2
etc.

季节

seasons_id  | title  | userid
----------------------------
1           | 2011   | 3
2           | 2012   | 10
3           | 2013   | 5

我的查询:

SELECT s.title, p.username, SUM(points) FROM matchdays m
INNER JOIN players p ON p.userid = m.userid
INNER JOIN seasons s ON m.userid = s.userid
group by s.season_id

这导致(示例！):

title | username | SUM(points)
------------------------------
2011  | user3    | 3744
2012  | user10   | 3457
2013  | user5    | 3888

应该是一张表，其中列出每个赛季的获胜者(最高分).目前，标题和用户名是正确的，但是总和太高了.我不知道算出了什么总和.理想情况下，总和是每个用户每个季节的每个比赛日的总和.

What it should look like is a table with the winner (max points) of every season. Right now, the title and username is correct, but the sum of the points is way too high. I couldn't figure out what sum is calculated. Ideally, the sum is the addition of every matchday of a season for every user.

推荐答案

您的主要问题是仅按季节分组.因此，无论哪个球员，您的SUM都在一个赛季的所有积分上运行.

Your main issue is that you group by seasons only. Thus your SUM is running on all points over a season, regardless of the player.

无论如何，整个方法都是错误的.在季节表中具有用户标识的缺陷"是最大的问题，您似乎也知道. 我将向您说明如何一次一次计算数据库中的排名，并随时让他们使用，这将为您节省很多麻烦，并且显然也节省了一些CPU和加载时间.

首先创建一个新表"Rankings":

The whole approach is wrong anyway. The "flaw" with userid in the season table is your biggest issue, and you seem to know it. I will explain you how to calculate your rankings in the database one time for all, and to have them at your disposal at all times, which will save you a lot of headaches, and obviously save some CPU and loading times as well.

CREATE table rankings (season_id INT, userid INT, points INT, rank INT)

如果您有很多玩家，请为除points

If you have a lot of players, index all columns but points

这是一个单发操作，每个季节结束时都要运行. 因此，暂时来说，每个季节您必须运行几次.

This is a oneshot operation to run each time a season has ended. So for the time being, you will have to run it several times for each season.

关键是计算每个球员在赛季中的排名，这是必不可少的，以后将非常有用.因为MySQL没有为此提供窗口功能，所以我们必须使用一个古老的技巧:增加一个计数器.

The key here is to compute the rank of each player for the season, which is a must-have that will be super-handy for later. Because MySQL doesnt have a window function for that, we have to use an old trick : incrementing a counter.

我分解.

这将计算一个赛季的得分，并提供该赛季的排名:

This will compute the points of a season, and provide the ranking for that season:

SELECT season_id, userid, SUM(points) as points 
FROM matchdays
WHERE season_id = 1
GROUP BY season_id, userid
ORDER BY points DESC

现在，我们调整此查询以添加一个排名列:

Now we adapt this query to add a rank column :

SELECT
   season_id, userid, points,
   @curRank := @curRank + 1 AS rank
FROM
(   
    SELECT season_id, userid, SUM(points) as points 
    FROM matchdays
    WHERE season_id = 1
    GROUP BY season_id, userid
)   T, 
(
    SELECT @curRank := 0
) R
ORDER BY T.points DESC

就是这样. 现在，我们可以将计算结果插入到我们的排名表中，以永久存储一次:

That's it. Now we can INSERT the results of this computation into our ranking table, to store it once for good :

INSERT INTO rankings
SELECT
   season_id, userid, points,
   @curRank := @curRank + 1 AS rank
FROM
(   
    SELECT season_id, userid, SUM(points) as points 
    FROM matchdays
    WHERE season_id = 1
    GROUP BY season_id, userid
)   T, 
(
    SELECT @curRank := 0
) R
ORDER BY T.points DESC

更改season_id = 1，然后每个季节重复一次. 将此查询保存在某个地方，以后，每当一个季节结束，就运行一次.

Change the season_id = 1 and repeat for each season. Save this query somewhere, and in the future, run it once each time a season has ended.

现在，您拥有适当的数据库计算排名和漂亮的排名表，可以随时查询.

Now you have a proper database-computed ranking and a nice ranking table that you can query whenever you want.

您想要每个赛季的胜利者吗?就这么简单:

You want the winner for each season ? As simple as that:

SELECT S.title, P.username, R.points
FROM Ranking R
INNER JOIN seasons S ON R.season_id=S.season_id
INNER JOIN players P ON R.userid=P.userid
WHERE R.rank = 1

随着时间的流逝，您会发现您可以通过排名表非常简单地完成很多不同的事情.

You will discover over the time that you can do a lot of different things very simply with your ranking table.

这篇关于选择对行有限制的MAX值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！