Sql改ページ文

今日は暇です。Sqlで文章を分けてテストしました。ネットでいくつかのデータを探しました。まとめたSqlのページは5種類に分けられています。

--  1，not in/top

select top 50 * from pagetest 

where id not in (select top 9900 id from pagetest order by id)

order by id



--  2，not exists

select top 50 * from pagetest 

where not exists 

(select 1 from (select top 9900 id from pagetest order by id)a  where a.id=pagetest.id)

order by id



--  3，max/top

select top 50 * from pagetest

where id>(select max(id) from (select top 9900 id from pagetest order by id)a)

order by id



--  4，row_number()

select top 50 * from 

(select row_number()over(order by id)rownumber,* from pagetest)a

where rownumber>9900



select * from 

(select row_number()over(order by id)rownumber,* from pagetest)a

where rownumber>9900 and rownumber<9951



select * from 

(select row_number()over(order by id)rownumber,* from pagetest)a

where rownumber between 9901 and 9950



--  5， csdn       ，row_number()   ，             ，           ，                   

select *

from (

    select row_number()over(order by tempColumn)rownumber,*

    from (select top 9950 tempColumn=0,* from pagetest where 1=1 order by id)a

)b

where rownumber>9900

 簡単な大データテストをして、百万のデータを使って、上の5種類のsql文の実行効率時間を測定します。

create database DBTest

use DBTest



--     

create table pagetest

(

id int identity(1,1) not null,

col01 int null,

col02 nvarchar(50) null,

col03 datetime null

)



--1    

declare @i int

set @i=0

while(@i<1000000)

begin

    insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate()

    set @i=@i+1

end
---  sql       
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()

<.....YOUR CODE.....>

select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '  '

 テストを経て、row_を使います。number()方法の効率は最も高く、使う時は最も少ないです。