SQL Server获取表中中间连续的记录（不用where语句）

最新推荐文章于 2023-03-15 22:32:48 发布

guchuanlong

最新推荐文章于 2023-03-15 22:32:48 发布

阅读量1.3k

点赞数

分类专栏： Windows编程 SQLServer

15 篇文章 0 订阅

订阅专栏

3 篇文章 0 订阅

订阅专栏

表pictures中有两个字段：id与title。id是自动编号的

表中有5条记录：1--p1,2--p2,3--p3,4--p4,5--p5

一、找到了一个小规律

string sqlstr = "select top 4 * from pictures order by id desc "; //查询结果p5,p4,p3,p2---说明是整个表先进行排序，再进行查询的

string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) "; //-------p5,p4,p3

string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) order by id desc";//-------p5,p4,p3

string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) order by id asc"; //-------p2,p3,p4

二、获取单条记录：

假设表中一共有counts条记录，现在想要查询第n条记录，则sql语句应是：

select top 1 * from (select top (counts-n+1) * from pictures order by id desc) order by id asc

第三条记录：

string sqlstr = "select top 1 * from (select top 3 * from pictures order by id desc) order by id asc";//-------p3

三、获取表中多条连续的记录

假设表中一共有counts条记录，现在想要查询第n到第m条的记录，则sql语句应是：

select top (m-n+1) * from (select top (counts-n+1) * from pictures order by id desc) order by id asc

获取第二到第四条记录：

string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) order by id asc";//-------p2,p3,p4

关注

专栏目录