sql 查询一个表中最新数据 并且从后第3行到第6行数据？

[wwzyyl9113]

sql 查询一个表中从第3行到第6行数据？

表 login
字段 id name time
比如表中有7条数据
id name time
1 a 2010-12-1 13:12:52
2 b 2010-12-2 13:12:52
3 c 2010-12-3 13:12:52
4 d 2010-12-4 13:12:52
5 e 2010-12-5 13:12:52
6 f 2010-12-6 13:12:52
7 g 2010-12-7 13:12:52
...
...

我想查询 这个表中 时间最新的并且从后面第2条数据开始查询 查询出3条数据出来
sql语句怎么写？

比如查询结果是
3 c 2010-12-3 13:12:52
4 d 2010-12-4 13:12:52
5 e 2010-12-5 13:12:52
...
...

[wuyq11]

select top m * into 临时表(或表变量) from tablename order by columnname
set rowcount n
select * from 表变量 order by columnname desc

;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m

[End]

[Quote=引用 5 楼 q107770540 的回复:]

SQL code
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].time DESC) AS [ROW_NUMBER], [t0].ID,[t0].name,[t0].time
FROM [login] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 3 A……
[/Quote]
UP THIS

[free_live_zyx]

select top 3 * from login where id not in(select top 2 id from login)

[free_live_zyx]

[Quote=引用 1 楼 shatamadedongxi 的回复:]
select top 3 * from login where id not in(select top 2 id from login)
[/Quote]
应该是:
select top 4 * from login where id not in(select top 2 id from login)

[xiaoqiu1234]

select top 3 * from login where id not in(select top 2 id from login) 可以实现吗

[free_live_zyx]

[Quote=引用 3 楼 xiaoqiu1234 的回复:]
select top 3 * from login where id not in(select top 2 id from login) 可以实现吗
[/Quote]

我测试过了,可以

[q107770540]

SELECT *
FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY [t0].time DESC) AS [ROW_NUMBER], [t0].ID,[t0].name,[t0].time
    FROM [login] AS [t0]
	) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 3 AND 5
ORDER BY [t1].[ROW_NUMBER]

[wwzyyl9113]

select top 3 * from login where id not in(select top 2 id from login)

如果数据库里有10条数据 这个是不是就不好用了，
如果有10条数据，我就想从后第8条开始查询，查询出3条出来
这个该怎么写呢？

[free_live_zyx]

[Quote=引用 2 楼 shatamadedongxi 的回复:]
引用 1 楼 shatamadedongxi 的回复:
select top 3 * from login where id not in(select top 2 id from login)

应该是:
select top 4 * from login where id not in(select top 2 id from login)
[/Quote]

如果要时间最新的话
select top 4 * from login where id not in(select top 2 id from login) order by time desc

[PitTroll]

select * from (select row_number() over(order by [time] asc) as row,* from login)t where row>1 and row<5