Select MAX value with restriction to rows

问题:

I have 3 tables:

matchdays:

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

players

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

seasons

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

My query:

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

This results in (example!):

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.

回答1:

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.

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.

Start by creating a new table "Rankings":

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

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

Then, populate the table for each season:

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.

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.

I decompose.

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

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.

回答2:

You're join is wrong, try something like:

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

As pointed out, userid does'nt belong/is not needed in 'seasons' table.