如何查找具有重复值的 MIN 并将 MIN 值替换为 varchar

Question

我试图找到某个季节中某个ID的最小值，并用缩写替换该值。

我有这两张表：

table: batplayerlevelyear                    table: levels
-------------------------                    --------------------------
pid   season   levelid                       levelid abbrev
-------------------------                    --------------------------
121   2008     3                             1       MLB
121   2008     4                             2       AAA
121   2008     5                             3       AA
121   2009     1                             4       A+
121   2009     2                             5       A
121   2010     1                             6       A-
122   2009     4                             7       R
122   2009     3
122   2010     3
122   2011     2

我想做的是：

table: newtable
--------------------
pid   season  abbrev
--------------------
121   2008    AA
121   2009    MLB
121   2010    MLB
122   2009    AA
122   2010    AA
122   2011    AAA

这是我迄今为止尝试过的那种代码，几乎给了我想要的东西，但替换了缩写而不是levelid

select
batplayerlevelyear.pid,
batplayerlevelyear.season,
min(levels.levelid) as highest_level
from batplayerlevelyear
leftjoin levels on batplayerlevelyear.levelid=levels.levelid
group by pid, season
order by pid

Answer 1

试试这个：

SELECT a.pid, a.season, l.abbrev
FROM   (SELECT batplayerlevelyear.pid,
               batplayerlevelyear.season,
               Min(levels.levelid) AS highest_level
        FROM   batplayerlevelyear
        GROUP  BY pid, season) a
       LEFT JOIN levels l ON a.highest_level = l.levelid  

Answer 2

相关子查询工作

;with batplayerlevelyear AS (
    select 121 pid, 2008 season, 3 levelid union all
    select 121 pid, 2008 season, 4 levelid union all
    select 121 pid, 2008 season, 5 levelid union all
    select 121 pid, 2009 season, 1 levelid union all
    select 121 pid, 2009 season, 2 levelid union all
    select 121 pid, 2010 season, 1 levelid union all
    select 122 pid, 2009 season, 4 levelid union all
    select 122 pid, 2009 season, 3 levelid union all
    select 122 pid, 2010 season, 3 levelid union all
    select 122 pid, 2011 season, 2 levelid 
),
levels AS (
    select 1 levelid, 'MLB' abbrev union all
    select 2 levelid, 'AAA' abbrev union all
    select 3 levelid, 'AA' abbrev union all
    select 4 levelid, 'A+' abbrev union all
    select 5 levelid, 'A' abbrev union all
    select 6 levelid, 'A-' abbrev union all
    select 7 levelid, 'R' abbrev 
)
select
batplayerlevelyear.pid,
batplayerlevelyear.season,
min(batplayerlevelyear.levelid) highest_level,
(select levels.abbrev from levels where levelid = min(batplayerlevelyear.levelid)) abbrev
from batplayerlevelyear
group by pid, season
order by pid