在每个组中选择SECOND LAST记录 [英] Select the SECOND LAST record in each group

问题描述

有一个表Remark，其中包含数据，如下所示:

There is a table Remark that contains data as shown below:

       SerialNo | RemarkNo  | Desp
=============================================
             10 |         1 | rainy
             10 |         2 | sunny
             11 |         1 | sunny
             11 |         2 | rainy
             11 |         3 | cloudy
             11 |         4 | sunny
             12 |         1 | rainy

哪个查询将返回以下结果:

What query will return the following result:

             10 |         1 | rainy
             11 |         3 | cloudy
             12 |      null | null

也就是说，应该返回每个组中的倒数第二个记录吗?

That is, the second last record in each group should be returned?

假定序列号的所有RemarkNo是连续的.备注编号越大，则备注进行得越晚.因此，序列号10的倒数第二个RemarkNo为1，且带有Desp'rainy'.

Assuming all the RemarkNo for a SerialNo are continuous. The larger the remark number, the later the remark was made. Hence, the second last RemarkNo for SerialNo 10 is 1 with Desp 'rainy'.

推荐答案

尝试:

select s.SerialNo, r.RemarkNo, r.Desp
from (select SerialNo, max(RemarkNo) maxRemark from Remark group by SerialNo) s
left join Remark r on s.SerialNo = r.SerialNo and s.maxRemark-1 = r.RemarkNo

((此处的SQLFiddle.)

这篇关于在每个组中选择SECOND LAST记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！