我的架构
我有以下表格
table notes/example values
------------------------------------------------
users (
id
email # "foo@example.com"
)
games (
id
name # "Space Invaders", "Asteroids", "Centipede"
)
players (
id
name # "uber dude"
user_id # player belongs to user
game_id # player belongs to game
)
scores (
id
player_id # belongs to one player
value # 50
created_at # "2010-09-10", "2010-08-05"
month # "2010-09", "2010-08"
)
我需要创建两个报告。
1)顶级选手
最近4个月表现最好的球员(每个球员的所有分数之和)。显示每个月的前10名。
2010-07 2010-08 2010-09 2010-10
1 plyA 5,000 pts plyB 9,400 pts ... ...
Centipede Solitaire
2 plyB 3,600 pts plyC 8,200 pts ... ...
Asteroids Centipede
3 plyC 2,900 pts plyA 7,000 pts ... ...
Centipede Centipede
4 ... ... ... ...
5 ... ... ... ...
6 ... ... ... ...
7 ... ... ... ...
8 ... ... ... ...
9 ... ... ... ...
10 ... ... ... ...
2)热门用户:
最近4个月表现最好的用户(每个用户的每个球员的所有分数之和)。显示每个月的前10名。
2010-07 2010-08 2010-09 2010-10
1 userA 50,000 pts userB 51,400 pts ... ...
2 userB 40,500 pts userA 39,300 pts ... ...
3 userC 40,200 pts userC 37,000 pts ... ...
4 ... ... ... ...
5 ... ... ... ...
6 ... ... ... ...
7 ... ... ... ...
8 ... ... ... ...
9 ... ... ... ...
10 ... ... ... ...
MySQL视图辅助对象
出于加入的目的,我有一个存储视图来帮助查询报告的月份。它将始终返回最近4个月。
report_months (
month
)
SELECT * FROM report_months;
2010-07
2010-08
2010-09
2010-10
问题所在
例如,在报告#1中,我可以很容易地获得总和。
select
p.name as player_name,
g.name as game_name,
s.month as month,
sum(s.score) as sum_score
from players as p
join games as g
on g.id = p.game_id
join scores as s
on s.player_id = p.id
join report_months as rm -- handy view helper
on rm.month = s.month
group by
p.name, g.name
order by
sum(s.score) desc
-- I can't do this :(
-- limit 0, 40
然而,我不能简单地获取前40个结果并将它们分散到4个月,因为这不能保证我每个月都有10个结果。
问题是
我如何修改我的查询,以确保我每个月得到10个?
发布于 2010-10-21 04:20:40
我不会像您所展示的那样,尝试进行按月制表的SQL查询。
取而代之的是按行查询每月排名前10的玩家,而不是按列:
Month Rank Player TotalScore Game
2010-07 1 plyA 5,000 pts Centipede
2010-07 2 plyB 3,600 pts Asteroids
2010-07 3 plyC 2,900 pts Centipede
...
2010-08 1 plyB 9,400 pts Solitaire
2010-08 2 plyC 8,200 pts Centipede
2010-08 3 plyA 7,000 pts Centipede
...
这就变成了一个greatest-n-per-group
问题,其中n
为10。
CREATE VIEW PlayerScoresByMonth AS
SELECT month, player_id, SUM(value) AS score
FROM scores
GROUP BY month, player_id;
SELECT s1.month, COUNT(s2.month)+1 AS Rank, s1.player_id, s1.score AS TotalScore
FROM PlayerScoresByMonth s1
LEFT OUTER JOIN PlayerScoresByMonth s2 ON s1.month = s2.month
AND (s1.score < s2.score OR s1.score = s2.score AND s1.player_id < s2.player_id)
GROUP BY s1.month, s1.player_id
HAVING COUNT(*) < 10
ORDER BY s1.month, Rank;
(这是未经测试的,但应该可以帮助您入门)
然后,您需要编写一些应用程序代码来获取此查询的结果,并按月份分隔列表,然后以您打算执行此操作的方式显示数据。
https://stackoverflow.com/questions/3981465
复制相似问题