SQL查询返回数十年来的最大值 [英] SQL Query to return maximums over decades

问题描述

这是使用MySQL 我的问题如下.我有一个棒球数据库，在那个棒球数据库中有一个主表，其中列出了曾经玩过的所有球员.还有一个击球表，该表跟踪每个玩家的击球统计数据.我创建了一个视图，将这两个视图结合在一起；因此masterplusbatting表. 我现在想找到自棒球开始以来每十年最高的HR命中率.这是我尝试过的.

THIS IS USING MYSQL My question is the following. I have a baseball database, and in that baseball database there is a master table which lists every player that has ever played. There is also a batting table, which tracks every players' batting statistics. I created a view to join those two together; hence the masterplusbatting table. I now want to find the highest HR hitter in each decade since baseball began. Here is what I tried.

    select f.yearID, truncate(f.yearid/10,0) as decade,f.nameFirst, f.nameLast, f.HR
    from (
    select yearID, max(HR) as HOMERS
    from masterplusbatting group by yearID
    )as x inner join masterplusbatting as f on f.yearID = x.yearId and f.HR = x.HOMERS
    group by decade

您可以看到我为了获取187, 188, 189等而不是1897, 1885,截断了yearID.然后，我按十年进行分组，认为这将使我每十年获得最高收益，但它并没有返回正确的值.例如，它在2004年给了我Adrian Beltre 48 HR，但每个人都知道，Barry Bonds在2001年达到了73 HR.有人能给我一些建议吗?

You can see that I truncated the yearID in order to get 187, 188, 189 etc instead of 1897, 1885,. I then grouped by the decade, thinking that it would give me the highest per decade, but it is not returning the correct values. For example, it's giving me Adrian Beltre with 48 HR's in 2004 but everyone knows that Barry Bonds hit 73 HR in 2001. Can anyone give me some pointers?

推荐答案

SELECT
  Lookup.DecadeID,
  Data.*
FROM
(
  SELECT
    truncate(yearid/10,0) as decadeID,
    MAX(HR) as Homers
  FROM
    masterplusbatting
  GROUP BY
    truncate(yearid/10,0)
)
  AS lookup
INNER JOIN
  masterplusbatting AS data
    ON  data.yearid >= lookup.decadeID * 10
    AND data.yearid <  lookup.decadeID * 10 + 10
    AND data.HR     =  lookup.homers

为MySQL编辑

这篇关于SQL查询返回数十年来的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！