一千萬個為什麽

搜索

在一定時間內計算最大值

我需要以下查詢的幫助,我將發布一個表的示例。

     player | goals | gamedate

playername1 |  1    | 2011-01-01
playername2 |  2    | 2011-01-01
playername1 |  1    | 2011-01-02
playername3 |  1    | 2011-01-01
playername1 |  2    | 2011-01-03
playername1 |  1    | 2011-01-01
playername3 |  1    | 2011-01-01
playername2 |  2    | 2011-01-01
playername1 |  1    | 2011-01-04

還有更多的數據,我想知道如何編寫一個查詢,在一段時間(遊戲季節)之間為頂級玩家提供最多的目標?

編輯:似乎我在數據結構的方式上有誤。對不起,我很抱歉。我將離開上一張桌子,以便每個人都可以看到第一個答案。我不想打開一個新問題,因為它基本上是一回事。我的道歉Haim。

`table1`
  id | gamedate

1    | 2011-01-01
2    | 2011-01-02
3    | 2011-01-03
4    | 2011-01-04
5    | 2011-01-05


`table2`
id | gameid  |      player | goals

 1 |   1     |  playername1 |  1  
 2 |   1     |  playername2 |  2  
 3 |   2     |  playername1 |  1 
 4 |   1     |  playername3 |  1
 5 |   3     |  playername1 |  2
 6 |   4     |  playername1 |  1
 7 |   2     |  playername3 |  1
 8 |   3     |  playername2 |  2
 9 |   5     |  playername1 |  1

其中 gameidtable1 中ID引用的外鍵。

最佳答案

替換您要檢查的日期, 並將限制(在示例中為5)設置為您想要的玩家數量。

SELECT player , sum(goals) AS top_scores FROM mytable 
WHERE gamedate BETWEEN '2011-01-02' AND '2011-01-07'
GROUP BY player ORDER BY top_scores DESC  LIMIT 5

編輯(根據問題編輯,你只需要添加一個連接):

SELECT player , sum(goals) AS top_scores FROM table2 t2
LEFT JOIN table1 t1  ON t1.id = t2.gameid
WHERE gamedate BETWEEN '2011-01-02' AND '2011-01-07'
GROUP BY player ORDER BY top_scores DESC  LIMIT 5

轉載註明原文: 在一定時間內計算最大值