mysql - 如何获取具有第二高值的所有行

Question

我有下表:

name  value  year
 A      1    2015
 A      2    2014
 A      3    2013
 B      1    2015
 B      3    2013
 C      1    2015
 C      2    2014

对于每个名字，我怎样才能得到第二大年份的行，如下所示:

name  value  year
 A      2    2014
 B      3    2013
 C      2    2014

我尝试了以下查询但没有成功:

select name, value, year
from TABLE_NAME
WHERE year IN (select year from TABLE_NAME order by year desc limit 1,1)

之前的查询给了我这个错误:"SQL 错误 (1235): 此版本的 MySQL 尚不支持 'LIMIT & IN/ALL/ANY/SOME 子查询' "

而且我现在无法更改 MySQL 版本 (5.6.25)，因为该解决方案已经投入生产。

有什么帮助吗？

Answer 1

在 MySQL 中解决每组 n 的一种方法是模拟 ROW_NUMBER。请注意，这只会为每个名称返回一个值。

SELECT 
    name, 
    value,
    year
FROM 
    (SELECT 
        t.name, 
        t.value,
        t.year,
        @rn := if(@prev = t.name, @rn + 1,1) as rn,
        @prev:=t.name

    FROM
        test_table as t
        JOIN (SELECT @Prev:= Null, @Rn := 0) as v
    ORDER BY 
        t.name,
        T.year desc) as t
WHERE
    rn = 2;

这是如何运作的。

• SELECT @Prev:= Null, @Rn := 0 初始化两个变量@Prev 和@Rn。
• @rn := if(@prev = t.name, @rn + 1,1) as rn 根据 if @ 将 @rn 的变量设置为 1 或 @rn + 1 prev = t.Name 并返回@rn 的值作为列rn
• @prev:=t.name 设置@prev 的值等于name 的当前值

如果你跑

SELECT 
    t.name, 
    t.value,
    t.year,
    @prev = t.name as eval,
    @rn := if(@prev = t.name, @rn + 1,1) as rn,
    @prev:=t.name as prev

FROM
    test_table as t
    JOIN (SELECT @Prev:= Null, @Rn := 0) as v
ORDER BY 
    t.name,
    T.year desc

我希望是这样的

name  value  year  eval  rn  prev
 A      1    2015  false  1  null
 A      2    2014  true   2  A
 A      3    2013  true   3  A
 B      1    2015  false  1  A
 B      3    2013  true   2  B
 C      1    2015  false  1  B
 C      2    2014  true   2  C

包装到子查询中并过滤 rn=2 给你想要的结果