时间戳

首页 > 解决方案 > 如何根据 RANK 找到团队的最小值和最大值

mysql - 如何根据 RANK 找到团队的最小值和最大值

问题描述

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

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

这里的列解释了 Teams Table Columns

    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

标签： mysqlsql

解决方案

这样的事情可能会起作用：

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;

推荐阅读