建站
咨詢
售后
建站咨詢
新聞中心
SQL Server分頁查詢是我們經(jīng)常會(huì)用到的功能，其實(shí)現(xiàn)方法也有很多，本文的幾種分頁方法摘自《SQL Server2005性能調(diào)優(yōu)》一書。希望對您學(xué)習(xí)SQL Server分頁查詢方面能有所幫助。
創(chuàng)新互聯(lián)不只是一家網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司；我們對營銷、技術(shù)、服務(wù)都有自己獨(dú)特見解，公司采取“創(chuàng)意+綜合+營銷”一體化的方式為您提供更專業(yè)的服務(wù)！我們經(jīng)歷的每一步也許不一定是最完美的，但每一步都有值得深思的意義。我們珍視每一份信任，關(guān)注我們的成都網(wǎng)站建設(shè)、成都網(wǎng)站制作質(zhì)量和服務(wù)品質(zhì)，在得到用戶滿意的同時(shí)，也能得到同行業(yè)的專業(yè)認(rèn)可，能夠?yàn)樾袠I(yè)創(chuàng)新發(fā)展助力。未來將繼續(xù)專注于技術(shù)創(chuàng)新，服務(wù)升級，滿足企業(yè)一站式成都全網(wǎng)營銷推廣需求，讓再小的高端網(wǎng)站設(shè)計(jì)也能產(chǎn)生價(jià)值！
用以下腳本生成測試數(shù)據(jù)：
- CREATE TABLE TRANS_TABLE(
- MYID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
- MYDESC VARCHAR(10),
- MYDATE DATETIME,
- MYGROUPID INT)
- DECLARE @I INT
- SET @I = 0WHILE @I < 1000000
- BEGIN
- INSERT INTO TRANS_TABLE
- SELECT CHAR(ASCII('A') - 2 + (2 * (1 + ABS(CHECKSUM(NEWID())) % 26))),
- DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '01/01/2007'),
- (ABS(CHECKSUM(NEWID())) % 10)
- SET @I = @I + 1
- END
- CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE
- ON TRANS_TABLE(MYDATE)
- CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID
- ON TRANS_TABLE(MYGROUPID)
1、基于CTE分頁
1）用row_number()排名函數(shù)，派生表的方式分頁
- DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int
- SELECT @START_ROW = 1, @MAX_ROWS = 25
- select *
- from ( select p.*, rownum rnum
- FROM (
- SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *
- FROM TRANS_TABLE (NOLOCK)
- ) p
- where rownum <= @START_ROW + @MAX_ROWS - 1
- )
- z where rnum >= @START_ROW
2）用CTE方式取代派生表
- DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
- SELECT @START_ROW = 1, @MAX_ROWS = 25;
- WITH PAGED AS (
- SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *
- FROM TRANS_TABLE (NOLOCK)
- )
- SELECT *
- FROM PAGEDWHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS-1
3）也是CTE方法，但是根據(jù)測試數(shù)據(jù)顯示這種性能比前兩種都好
- DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
- SELECT @START_ROW = 1, @MAX_ROWS = 25;
- WITH PAGED AS (
- SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
- FROM TRANS_TABLE (NOLOCK)
- )
- SELECT TT.*
- FROM PAGED PGD
- INNER JOIN TRANS_TABLE TT
- ON PGD.MYID = TT.MYID
- WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
- ORDER BY MyDate, MYID
2、 基于ROW_COUNT的分頁
- DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int,
- @START_DATETIME DATETIME, @TOT_ROW_CNT INT
- SELECT @START_ROW = 1, @MAX_ROWS = 25
- -- Get the first row for the page
- SET ROWCOUNT @START_ROW
- SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)
- ORDER BY MYDATE, MYID
- -- Now, set the row count to MaximumRows and get
- -- all records >= @first_idSET ROWCOUNT @MAX_ROWS
- SELECT *
- FROM TRANS_TABLE (NOLOCK)
- WHERE MYID >= @START_ROW
- AND MYDATE >= @START_DATETIME
- ORDER BY MYDATE, MYID
- SET ROWCOUNT 0
3、 TOP @X分頁
SQL Server 2005中可以把返回行數(shù)做為參數(shù)傳給top語句。
- DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT INT, @START_DESC VARCHAR(10)
- SELECT @START_ROW = 1, @MAX_ROWS = 25
- -- Get the first row for the page
- SELECT TOP(@START_ROW) @START_ID = MYID, @START_DESC = MYDESC FROM TRANS_TABLE (NOLOCK)
- ORDER BY MYDESC, MYID
- SELECT TOP(@MAX_ROWS) *
- FROM TRANS_TABLE (NOLOCK)
- WHERE MYID >= @START_ROW
- AND MYDESC >= @START_DESC
- ORDER BY MYDESC, MYID
4、 Temp表分頁
- DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
- SELECT @START_ROW = 1, @MAX_ROWS = 25;
- SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,
- MYID
- into #TEMP
- FROM TRANS_TABLE (NOLOCK)
- SELECT TT.*
- FROM TRANS_TABLE (NOLOCK) TT
- INNER JOIN #TEMP TON TT.MYID = T.MYID
- WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
- DROP TABLE #TEMP
以上便是這次為您介紹的 SQL Server 2005中幾種分頁方法，希望對您學(xué)習(xí)SQL Server分頁查詢方面能有所幫助。
本文標(biāo)題：淺談SQL Server2005的幾種分頁方法
網(wǎng)頁地址：http://www.dlmjj.cn/article/cojsjho.html
