茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事,不可能遺忘,只是想不起來而已!
 恭喜您是本站第 1682740 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
11081021552972_107.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [轉貼]SQL分頁select

發表者 討論內容
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]SQL分頁select
SQL分頁select
分頁前SQL
    SELECT [欄位1, 欄位2, ...]
    FROM [資料表]
    WHERE [篩選條件]
    ORDER BY [排序欄位] [ASC/DESC]MS-SQL

MS-SQL Type A
無排序,在關聯式查詢時,可能導致每次查詢同一分頁的資料時,出來的資料都不一樣。
    SELECT [欄位1, 欄位2, ...] FROM [資料表]
    WHERE [主要索引欄位] IN
    (
        SELECT * FROM
        (
            SELECT TOP [每頁長度*第幾頁] [主要索引欄位]
                FROM [資料表]
                WHERE [篩選條件]
        )
        AS [資料表别名1]
        WHERE [主要索引欄位] NOT IN
        (
            SELECT TOP [每頁長度] [主要索引欄位]
                FROM [資料表]
                WHERE [篩選條件]
        )
    )

MS-SQL Type B
有排序,又稱【夾擠式分頁查詢】。MS-SQL 2000以前,常見的分頁用語法。
SELECT * FROM
(
    SELECT TOP [每頁長度] * FROM
    (
        SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...]
        FROM [資料表]
        WHERE [篩選條件]
        ORDER BY [排序欄位] [DESC/ASC]
    )
    AS [資料表别名1]
    ORDER BY [排序欄位] [ASC/DESC]
)
AS [資料表别名2]
ORDER BY [排序欄位] [DESC/ASC]

MS-SQL Type C
MS-SQL 2005以後才有RANK()語法。
SELECT * FROM
(
    SELECT rank() OVER ( ORDER BY [排序欄位] [ASC/DESC] ) AS RankNumber, * FROM
    (
        SELECT [欄位1, 欄位2, ...]
        FROM [資料表]
        WHERE [篩選條件]
    )
    AS [資料表别名1]
) AS [資料表别名2]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]

MS-SQL Type D
MS-SQL 2000可用,但Select時,不可包含PK用的欄位。(需透過暫存資料表操作)
參考資料:(SQL)抽獎,亂數抽出10筆中獎資料並排名
SELECT IDENTITY(INT,1,1) AS RankNumber, [欄位1, 欄位2, ...]
INTO [#暫存資料表名稱]
FROM [資料表]
ORDER BY [排序欄位] [ASC/DESC];

SELECT * FROM [#暫存資料表名稱]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁];

DROP TABLE [#暫存資料表名稱];

其他相關
當DataGrid遇見100萬筆資料
(以SQL預存程序實作SQL-Sever的北風資料庫的分頁範例)
SQL Server 2000 Paging and Sorting Using ROWCOUNT and SQL_VARIANT
MySQL
SELECT [欄位1, 欄位2, ...]
        FROM [資料表]
        WHERE [篩選條件]
        ORDER BY [排序欄位] [ASC/DESC]
        LIMIT [每頁長度] OFFSET [每頁長度*第幾頁]

Oracle
使用ROWNUM做分頁
SELECT * FROM (
        SELECT [欄位1, 欄位2, ...]
                FROM [資料表]
                WHERE [篩選條件]
                ORDER BY [排序欄位] [ASC/DESC]
)
WHERE   ROWNUM >= [每頁長度*(第幾頁-1)+1]
    AND ROWNUM <= [每頁長度*第幾頁]

參照:On Top-n and Pagination Queries


原文出處:SQL - ProgWiki
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]SQL Server 多種常見分頁語法

SQL Server 多種常見分頁語法,及最新分頁語法 OFFSET 跟 FETCH

2017-11-06

在SQL Server 2012以前SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2008 R2等版本,要實現分頁結果,通常使用以下三種方式:

表中主鍵必須為標識列,[ID] int IDENTITY (1,1)
第一種分頁SQL語法與說明:(利用Not In和SELECT TOP分頁)
SQL語法:
SELECT TOP 10 *
    FROM [Northwind].[dbo].[Orders]
    WHERE (ID NOT IN
        (SELECT TOP 20 id
            FROM [Northwind].[dbo].[Orders]
            ORDER BY id
        )
    )
    ORDER BY ID


SQL語法說明:
SELECT TOP 每頁希望顯示的筆數 *
    FROM 查詢資料表
    WHERE (ID NOT IN
        (SELECT TOP 每頁希望顯示的筆數*頁數 id
            FROM 查詢資料表
            ORDER BY id
        )
    )
    ORDER BY ID


第二種分頁語法與說明:(利用ID大於多少和SELECT TOP分頁)
SQL語法:
SELECT TOP 10 *
    FROM [Northwind].[dbo].[Orders]
    WHERE (ID >
        (SELECT MAX(id)
            FROM (SELECT TOP 20 id
                FROM [Northwind].[dbo].[Orders]
                ORDER BY id) AS T
            )
        )
        ORDER BY ID


SQL語法說明:
SELECT TOP 每頁希望顯示的筆數 *
    FROM 查詢資料表
    WHERE (ID >
        (SELECT MAX(id)
            FROM (SELECT TOP 每頁希望顯示的筆數*頁數 id
                FROM 查詢資料表
                ORDER BY id) AS T
            )

        )
        ORDER BY ID


第三種分頁語法與說明:(利用SQL的遊標存儲過程分頁)


create  procedure SqlPager
@sqlstr nvarchar(4000), --查詢字符串
@currentpage int, --第N頁
@pagesize int --每頁行數
as
set nocount on
declare @P1 int, --P1是遊標的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

第一種和第二種分頁語法,如果沒有主鍵,可以使用臨時資料表,另外也可以使用第三種分頁語法執行,但是執行效能會比較低。
為了資料庫效能考量,以第一種、第二種為主,並加上主鍵和索引,另外以最優化為優先時,當然又以第二種作為查詢效能會最高。

透過SQL 查詢效能分析器,顯示比較:我的結論是:
第二種分頁語法:(利用ID大於多少和SELECT TOP分頁)效能最高,需要拼接SQL語句
第一種分頁語法:(利用Not In和SELECT TOP分頁)   效能次之,需要拼接SQL語句
第三種分頁語法:(利用SQL的遊標存儲過程分頁)    效能最差,但是最為通用

 

另外,也有一些程式設計師為了要達到分頁的功能,

會將查詢到的結果集(所有筆數),傳到程式端處理,

在單機可能可以不用考慮相關效能問題,

但如果是應用在行動裝置的 APP 上,那可能就會有致命的問題了,


例如查詢結果集總共用有10,000筆,這樣在每次換頁時,就都須要查詢、傳送10,000筆,

在APP程式中再去處理出指定的頁數筆數,

光是想像,就知道在傳輸速度以及處理效能上,會有非常大的問題。

從 SQL Server 2005 一直到 SQL Server 20008R2,如果想要在資料庫中,取出已分頁的資料筆數,會使用 ROW_NUMBER() 這個分頁語法來處理。

語法如下:

SELECT * From(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderID DESC) num,*
    FROM [Northwind].[dbo].[Orders]
) a
WHERE num Between 101 And 150

透過以上的語法查詢 Orders 訂單資料表,並且使用 OrderID 訂單號碼由大至小排序,查詢第 101 到 150 共 50 筆的訂單資料。

 

到了 SQL Server 2012 微軟提供了一個新的專為分頁而設計的語法,主要是擴充了 Order By 的子句 OFFSET ROWS 跟 FETCH NEXT ROWS。

使用 OFFSET 和 FETCH 限制傳回的資料列。
讓你可以過濾篩選特定範圍的資料列。

提供了對結果集的分頁處理功能。
可以指定跳過的行數,指定要取回的資料列筆數。

而且,OFFSET 和 FETCH 子句是依據 draft ANSI SQL:2011 標準。
因此,會比 TOP 子句具備更好的 SQL 語言相容性。

ORDER BY {order_by_list}
OFFSET {offset_value} ROW(S)
ETCH FIRST|NEXT {fetch_value} ROW(S) ONLY

其中 OFFSET 是必要子句,不可以省略,但 OFFSET 是選擇性子句。

 

下面語法依據salesorderid欄位來排序,

SELECT * FROM [Northwind].[dbo].[Orders]
    ORDER BY OrderID
    OFFSET 100 ROWS
    FETCH NEXT 50 ROWS ONLY

上述的語法,說明如下。

主要以 OrderId 欄位作為排序的依據。

直接「跳過 100 筆資料」(OFFSET 100 ROWS)。

從「第 101 筆資料開始取得接下來的 50 筆」(FETCH NEXT 50 ROWS ONLY)

取得區段的訂單資料,也就依據這個特性來達到分頁的結果。

 

透過 SQL Server Profiler 偵測可以比較確定,透過 SQL Server 2012 所提供的新語法,使用的資源較少、速度較快,因此,強烈建議如果你SQL Server已經是 2012 以上的版本,就盡快改用新的分頁語法,來提高程式查詢分頁的效能吧。


原文出處:SQL Server 多種常見分頁語法,及最新分頁語法 OFFSET 跟 FETCH :: CooTAG庫特客
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]T-SQL 三種分頁查詢寫法

T-SQL筆記15_三種分頁查詢寫法_避免Server壓力過大Loading等待太久(支援自SQL2000,2005,2012後)

 在網頁中通常做表格資料的呈現最怕就是資料量大到幾千筆甚至到萬筆查詢Loading過久,Server負荷不來的情況。

此時在網頁上通常會做一些分頁減輕查詢耗費資源與時間成本
(每頁一次查詢只先呈現10~20、30幾筆之類的分頁小批量查詢機制)
而不一次就要將上萬筆資料先全查出來,等loading轉圈圈1分鐘甚至到5~8分鐘過後網頁資料才出現。

有了上述的概念後接著就記錄一下常用到的透過SQL分頁查詢語法段落


這裡的目標結果TABLE可能會是一個join起來的table集合也可能是單一一個temp table
看實際應用情境

以我的情境是join的情況
我要套分頁機制的  目標結果Table
是來自於這段SQL

這裡pat_patNo為專利的流水編號

寫法1.用最硬解的SELECT TOP方式, 可支援SQL 2000的環境



 1
2
3
4
5
6
7
8
9
10

25
SELECT TOP @PageSize *
FROM
}
WHERE
( IN
({具唯一跟可排序性欄位}
}
條記錄
DESC





寫法2.透過ROW_NUMBER()搭配OVER(ORDER BY 特定某個具唯一性跟可排序性的Field)跟BETWEEN來實踐
備註:ROW_NUMBER()是 自SQL Server2005開始就適用

具體寫法模式



 1
2
3
4
5
6
7
8
9
10
11
12
13

25
FROM
(
SELECT
*, ROW_NUMBER() OVER (as RowId
FROM
}
WHERE
Conditions
) AS vw
WHERE
RowId AND @PageIndex * @PageSize




我們可以藉由設定每頁要呈現多少筆資料的PageSize跟第幾頁的頁碼達到一個分頁Shift的功能


寫法3.藉由OFFSET搭配FETCH
備註:OFFSET跟FETCH是 自SQL Server 2012後才有的機制,因此如有環境比較老舊的情境就不適用。



1
2
3
4
5
6
7
8

25
FROM
}
BY
ASC/DESC
ROWS
ONLY;

這裡套用後機制

















Ref:

筆記-T-SQL 分頁查詢並傳回總筆數


[SQL Server]幾種分頁(Paging)寫法

SQL Server 2012 :分頁處理:認識 OFFSET 和 FETCH 子句

[MSSQL] 4種SQL分頁方式

一條SQL語句搞定Sql2000 分頁

[MSSQL]分頁

原文出處:T-SQL筆記15_三種分頁查詢寫法_避免Server壓力過大Loading等待太久(支援自SQL2000,2005,2012後)
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]巧用 SQL 實現查詢的高效分頁

巧用SQL實現查詢的高效分頁

2019-11-10  由 網雲技術 發表于 程式開發

朋友們,我們學習和使用資料庫技術時,是否常常會感覺,SQL就那些基礎語法,掌握了自己基本都可以無所不能了。有些朋友可能會為自己的自負栽跟頭了,比如別人寫的查詢運行很快,而自己寫的速度卻非常慢。

. . .

簡單的SQL查詢隱藏了很深的玄機

表面看,不就是select查詢嘛,so easy,還有誰比誰更先進的。但事實卻總不是這樣,就算是簡單如select,都包含了無窮的玄機。

當然,導致查詢速度慢的原因有很多,比如主鍵的設置和使用、join的合理應用、只返回必要的欄位列表等等。這些查詢優化的方法可能一本書都寫不完。

我們今天要講的,主要是如何巧妙使用分頁技術加快查詢響應的速度。所謂分頁,當然是把海量的數據(比如1000萬條),按照固定的頁大小(比如1000條),根據需要分多次(比如1萬次)返回。


. . .

如果我們面對的數據行數只有幾十條數百條,分頁就沒有什麼明顯的意義了,但如果是數十萬、數百萬行呢,如果您不分頁一次性返回,有沒有必要另說,哪像蝸牛一樣的速度絕對會讓您懷疑人生。

特別是在手機APP中,我們一般通過手指上滑獲取更多(其實是下一頁)數據,完全不需要一次性完整返回。分頁速度快,用戶的體驗當然要更好。

還好主流的資料庫都支持分頁查詢,但不同資料庫的分頁方法稍有不同,下面著重講解下SQL Server、MySQL和PostgreSQL三種主流資料庫的分頁語法和具體應用。

SQL Server的專業分頁語法

SQL Server從2012版本開始,提供了一種專注於分頁查詢的語法支持,其語法格式為:


offset m rows fetch next n rows only

其中m的含義為記錄起始行前一條記錄的行標,n的含義為返回記錄的行數。

. . .

如果我們定義@pageindex為頁碼,@pagesize為頁大小,其常用的語法格式一般為:


select 欄位列表 from 表
order by 排序欄位
offset (@pageindex-1)*@pagesize rows fetch next @pagesize rows only;

這裡,頁碼@pageindex代表第幾頁(從1開始),頁大小@pagesize代表每頁的記錄條數。

SQL Server分頁語法應用案例

我這裡準備了一個有16萬條記錄的淘寶熱詞表,我們按每頁100條記錄查詢,測試下運行效果,腳本如下:


declare @pageindex int=1;
declare @pagesize smallint=100;
select t1.*
from dwordhot t1

order by isnull(t1.fhotcount,0) desc

{!-- PGC_COLUMN --}

offset (@pageindex-1)*@pagesize
rows fetch next @pagesize rows only;

運行效果如下:

. . .

分頁查詢耗用時間是33毫秒,如果不分頁,速度就差多了,如下圖所示:

. . .

完整查詢耗用時間15951毫秒,差別不是一般的大。對於大數據量查詢的情況,查詢不分頁,簡直就是自找苦吃。

MySQL的專業分頁語法

MySQL的查詢分頁的非常簡單,要用到limit關鍵字,其語法格式為:


limit m,n

其中m的含義為起始索引(從0開始),n的含義為取n條數據,結合頁碼和頁大小,其語法格式一般體現為:


select 欄位列表 from 表
limit (@pageindex-1)*@pagesize, @pagesize;

. . .

MySQL分頁比較簡潔易懂,這裡不再舉例。

pgSQL的專業分頁語法

PostgreSQL的查詢分頁也支持offset關鍵字,但語法格式與SQLServer不太相同,同時她也支持limit關鍵字,似乎是綜合了SQLServer和MySQL語法。其語法格式為:


limit n offset m

其中n的含義為獲取的記錄條數,m的含義為查詢的起點位置(從0開始),結合頁碼和頁大小,其語法格式一般體現為:

. . .
select 欄位列表 from 表
limit @pagesize offset (@pageindex-1)*@pagesize

pgSQL的分頁語法應用案例


我這裡準備了一張表snnavi,存入1萬條記錄,腳本如下:


do $$
declare
vtest integer := 1;
begin
while vtest < 10000 loop
vtest = vtest+1;
insert into snnavi(fncsid,fnaviid)
values ( 9501, vtest);
end loop;
end $$;
select * from snnavi;

查詢耗時結果參看下圖:

. . .

如果我們使用分頁的方式返回第一頁的100行,速度就飛快了,如下圖所示:

. . .

其它分頁方式

如果您的查詢數據量比較大,強烈建議您使用分頁技術,而且儘量採用專業的推薦分頁方式,既然是專業的,這效率肯定是槓槓的。

除了上述的專業方式,我們還可以使用其它非專業的分頁方式。比如在SQLServer中,使用top、row_number、over等關鍵字,也可以實現查詢分頁,使用得當時其運行的效率與專業模式相差無幾。

如果您有興趣了解,可參看我之前寫的文章《如何在SQLServer查詢中實現高效分頁》。

. . .

無論何種方法,既然要分頁,就要知道行的起點,根據起點結合top、order by等關鍵字截取相應區間的記錄。如果您的表設置有整數遞增欄位,使用


where id>=起點 and id<=終點

會更加直觀,但並不是所有的表我們都會這樣做,所以掌握專業的分頁語法還是非常必要

的。


原文出處:巧用SQL實現查詢的高效分頁 - 每日頭條
前一個主題 | 頁首 | | |



Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|