如何根据排名找到团队的最小和最大值

Question

问:列出第一名和最后一名球队以及他们每年的击球次数SQL查询是什么，我如何在同一列中按年份对排名和分组结果执行min和Max操作？

下面是我尝试在其上编写SQL的Teams表的列

此处的列是说明的Teams表列

    yearID         Year
    lgID           League
    teamID         Team
    franchID       Franchise (links to TeamsFranchise table)
    divID          Team's division
    Rank           Position in final standings
    AB             At bats

期望输出

我试图在同一年的排名中实现最小和最大，但无法计算出如何在同一列的排名中获得最小最大值

   Team ID, Year, Rank, At Bats 
   PH1,     1871,  1,    1281
   RC1,     1871,  9,    1036
   LAA,     2014,  1,    5652

样本数据

 yearID lgId    teamID  franchID  divID   Rank    Ab(at-bats)
 '1902', 'AL', 'BOS',   'BOS',     '',    '3',   '4875'
 '1914', 'NL', 'BRO',   'LAD',     '',   '5',   '1386'
 '1925', 'AL', 'CLE',    'CLE',    '',   '6',   '1631'
 '1937', 'NL', 'NY1',    'SFG',    '',    '1',   '1484'

MySql脚本给我错误

select teamID,yearID,Rank,AB, 
RANK() OVER (PARTITION BY yearID order by Rank DESC) other_rank
from Teams;

错误

   ERROR 1064 (42000): You have an error in your SQL syntax; check the 
   manual that corresponds to your MySQL server version for the right 
   syntax to use near '(PARTITION BY yearID order by Rank DESC) other_
   rank from Teams' at line 1

Answer 1

像这样的东西可能会起作用：

with max_rank as (
    SELECT yearID, max(Rank) as MaxRank
    from Teams
    Group by yearID
)
SELECT T.*
FROM Teams T
LEFT OUT JOIN max_rank M ON T.yearID = M.yearid AND T.rank = m.MaxRank
WHERE T = 1 OR T.rank = M.MaxRank;