小编典典

如何获得所有第二高值的行

sql

我有这张桌子:

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),因为该解决方案已经投入生产。

有什么帮助吗?


阅读 209

收藏
2021-03-08

共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 将@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获得所需的结果

2021-03-08