sql server语言 - 获取sql中每组倒数第二的记录

Question

我知道如何获得组的最后一个记录，但我也需要获得第二个到最后一个记录。我该怎么做呢?下面是获取最后一条记录的代码。

select job qjob, max(id) qid from sqbclog group by job

Answer 1

SELECT * 
FROM (
select job AS qjob
      , id AS qid
      ,ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY ID DESC) AS RN
from sqbclog
)Sub
WHERE rn <= 2

此查询将返回最后两条记录，但如果您只需要最后第二条记录，则在where子句中使用where en = 2，否则保持原样

Answer 2

应该可以了

select * 
    from sqbclog 
    where id not in (
        select top (
            (select count(*) from sqbclog ) - 2
        ) id 
        from sqbclog 
    )

Answer 3

如果我正确理解了您的原始查询，并且它实际上给了您最后一行，那么TOP 2应该给您最后两行:

SELECT TOP 2 job qjob, max(id) qid从sqbclog按作业分组

Answer 4

select job qjob, max(id) -1 qid 
from sqbclog
group by job