mysql - 如何检索每个 ID 的最新和第二个最新日期？

Question

我的 SQL 数据库是这样的

ID DATE 
1  2017-01-01
1  2017-01-03
1  2017-01-05
2  2017-01-06 
2  2017-01-07
2  2017-01-08
2  2017-01-11
3  2017-01-11

如何检索每个 ID 的最大(日期)和第二个最新日期，如下所示。

 ID MAXDATE     SECONDMAXDATE        
 1  2017-01-05  2017-01-03  
 2  2017-01-11  2017-01-08
 3  2017-01-11  2017-01-11 (or 'NONE')

PS:当只有1条记录时，SECONDMAXDATE既可以显示MAXDATE也可以显示STRING(例如NONE)

Answer 1

我认为最简单的方法是 group_concat()/substring_index() “hack”:

select id, max(date),
       substring_index(substring_index(group_concat(date order by date desc), ',', 2), ',', -1) as second_date
from t
group by id;

注意:这会将第二个日期作为字符串而不是日期返回。如果愿意，您可以将其转换回日期。

此外，group_concat() 的默认最大长度为 1,024 字节。这对于大多数用途(大约一百个日期)来说已经足够了。如果每个 id 有更多日期，那么这种机制可能不是最好的机制。