sqlserver数据库中,表(table)中存放了同一时间(time)的多条数据,现在需要查询如:倒数第五次时间的数据,(平时用到的top获取数据,我只能定位到第一行或者最后一行)
根据时间降序(这里是倒数,所以我用desc,正数第五次用asc),然后group by分组,可以获取到倒数第五次的具体时间
select time
from (
select row_number() over (order by time desc) num, time
from table
group by time
) tmp
where tmp.num = 5 --分组之后,第5行就是需要的数据,需要第三行就 num = 3
或者用top会麻烦一点,先按需求排序获取前五行,然后再将这五行升序/升序获取第一行,以本题为例:
select top 1 tmp.time
from (
select top 5 time
from table
group by time
order by time desc
) tmp
order by tmp.time
完整代码(查询出倒数第五次时间的数据)
select *
from table
where time in (
select time
from (
select row_number() over (order by time desc) num, time
from table
group by time
) tmp
where tmp.num = 5
)
select *
from table
where time in (
select top 1 tmp.time
from (
select top 5 time
from table
group by time
order by time desc
) tmp
order by tmp.time
)