首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >如何用MySQL生成这两个报表?

如何用MySQL生成这两个报表?
EN

Stack Overflow用户
提问于 2010-10-21 03:14:47
回答 1查看 253关注 0票数 1

我的架构

我有以下表格

代码语言:javascript
运行
复制
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名。

代码语言:javascript
运行
复制
    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名。

代码语言:javascript
运行
复制
    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个月。

代码语言:javascript
运行
复制
report_months (
  month
)

SELECT * FROM report_months;

2010-07
2010-08
2010-09
2010-10

问题所在

例如,在报告#1中,我可以很容易地获得总和。

代码语言:javascript
运行
复制
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个?

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2010-10-21 04:20:40

我不会像您所展示的那样,尝试进行按月制表的SQL查询。

取而代之的是按行查询每月排名前10的玩家,而不是按列:

代码语言:javascript
运行
复制
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。

代码语言:javascript
运行
复制
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;

(这是未经测试的,但应该可以帮助您入门)

然后,您需要编写一些应用程序代码来获取此查询的结果,并按月份分隔列表,然后以您打算执行此操作的方式显示数据。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/3981465

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档