SQL分页查询

 

方法一：

-----------------查询10条数据，并增加排序id--------------

select top 10 ROW_NUMBER() over (order by fileid desc )as num,* from [Samdps_Test].[dbo].[UT_DealerSource]

--------------- 在上面查询的10条数据中分成5组每组2条，查询第5组数据，也就是8-10

select top 2* from ( select top 10 ROW_NUMBER()over (order by id desc )as num,* from [Samdps_Test].[dbo].[UT_DealerSource]

order by id desc)a ---order by fileid desc 防止数据位置变化

where num between (2*4+1) and (2*4+2)

 

方法二：

--------------------------------查询出前10行数据----------------------

select NTILE(5) OVER(ORDER BY filecode DESC ) as num,* from (select top 10 * from[Samdps_Test].[dbo].[UT_DealerSource])a 

---------------查询第5组数据-------------------

 

select * from (

select NTILE(5)over(order by id desc ) as num ,* from  (Select top 10* from [Samdps_Test].[dbo].[UT_DealerSource] order by id desc)a 

)b

where num=5

 

----------------------保险起见 --- 测试-------------

SELECT TOP 100 *

INTO #TEST

FROM [Samdps_Test].[dbo].[UT_DealerSource]

 

SELECT ROW_NUMBER() OVER (ORDER BY id ) AS [序号], NTILE(5) OVER(ORDER BY id) as z, *

INTO #TEST1

FROM #TEST

 

SELECT * FROM #TEST1

WHERE Z=3

 

DROP TABLE #TEST

DROP TABLE #TEST1