表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
转自:http://zshrong.blog.163.com/blog/static/184300254201142845055658/