我有这张桌子:
name value year A 1 2015 A 2 2014 A 3 2013 B 1 2015 B 3 2013 C 1 2015 C 2 2014如何为每个名称获得年份第二高的行，如下所示:
How can I get, for each name, the row with the second highest year, like this:
name value year A 2 2014 B 3 2013 C 2 2014我尝试了以下查询，但没有成功:
I tried the following query but no success:
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子查询""
The previous query gives me this error: "SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' "
由于该解决方案已经在生产中，所以我现在无法更改MySQL版本(5.6.25).
And I can't change the MySQL version (5.6.25) right now, because the solution is already in production.
有什么帮助吗?
推荐答案在MySQL中，每个组求解n的一种方法是模拟ROW_NUMBER.请注意，这只会为每个名称返回一个值.
One way to solve n per group in MySQL is to simulate ROW_NUMBER. Note that this will only return one value per name.
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 @Prev:= Null, @Rn := 0 initializes two variables @Prev and @Rn.
- @rn := if(@prev = t.name, @rn + 1,1) as rn set the variable of @rn to either 1 or @rn + 1 depending on if @prev = t.Name and returns the value of @rn as the column rn
- @prev:=t.name sets the value of @prev equal to the current value of 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进行过滤将为您提供所需的结果
Wrapping into a subquery and the filtering for rn=2 gives you the desired result