成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

SQL案例分析:窗口函數實現高效分頁查詢

數據庫 SQL Server
我們在使用 SQL 語句實現分頁查詢時,需要知道一些額外的參數信息,例如查詢返回的總行數、當前所在的頁數、最后一頁的頁數等。

我們在使用 SQL 語句實現分頁查詢時,需要知道一些額外的參數信息,例如查詢返回的總行數、當前所在的頁數、最后一頁的頁數等。

在傳統的實現方法中我們需要執行額外的查詢語句獲得這些信息,本文介紹一種只需要一個查詢語句就可以返回所有數據的方法,也就是通過 SQL 窗口函數實現高效的分頁查詢功能。

示例表和數據:https://github.com/dongxuyang1985/thinking_in_sql

傳統方法實現分頁查詢

在 SQL 中實現分頁查詢的傳統方法就是利用標準的 OFFSET … FETCH 語句或者許多數據庫支持的 LIMIT … OFFSET 語句,例如:

-- Oracle、SQL Server、PostgreSQL
SELECT emp_name, sex, email 
FROM employee
ORDER BY emp_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL、PostgreSQL、SQLite
SELECT emp_name, sex, email 
FROM employee
ORDER BY emp_id
LIMIT 10 OFFSET 10;

以上語句非常容易理解,返回的是第 2 頁中的 10 條記錄。但是問題在于我們如何知道總共包含多少頁數據(或者總的記錄數),顯然在此之前我們需要執行另一個查詢:

SELECT COUNT(*)
FROM employee;

COUNT(*)|
--------+
      25|

有了總的記錄數 25 之后,我們可以計算出數據共有 3 頁,每頁 10 條。

這種方法要求我們每次進行分頁查詢時都需要執行 2 個查詢語句,使用起來不是很方便。下面我們介紹更加高效的窗口函數分頁查詢。

關于分頁查詢的實現,OFFSET 分頁對于大量數據的分頁可能存在性能問題,另一種方法就是采用鍵集分頁(keyset pagination)。

窗口函數實現分頁查詢

首先讓我們考慮一下使用 OFFSET 分頁查詢時需要哪些參數:

  • TOTAL_ROWS,總記錄數;
  • CURRENT_PAGE,當前所在頁碼;
  • MAX_PAGE_SIZE,每一頁最多顯示的記錄數,例如 10、20、50;
  • ACTUAL_PAGE_SIZE,當前頁實際包含的記錄數;
  • ROW_NBR,每條記錄的實際偏移量;
  • LAST_PAGE,當前頁是否是最后一頁。

每一頁最多顯示的記錄數(MAX_PAGE_SIZE)是我們傳遞給數據庫的參數,其他則是查詢返回的結果,我們可以通過下面的查詢語句實現所有的功能:

-- Oracle、SQL Server、PostgreSQL
WITH e AS ( -- 初始查詢
  SELECT emp_id, emp_name, sex, email
  FROM employee
),
t AS (
  SELECT emp_id, emp_name, sex, email, 
         COUNT(*) OVER () AS total_rows, -- 總記錄數
         ROW_NUMBER () OVER (ORDER BY e.emp_id) AS row_nbr -- 偏移量,ORDER BY和初始查詢相同
  FROM e
  ORDER BY e.emp_id -- 排序
  OFFSET 10 ROWS -- 分頁
  FETCH NEXT 10 ROWS ONLY
)
SELECT
  emp_id, emp_name, sex, email,
  COUNT(*) OVER () AS actual_page_size, -- 當前頁實際記錄數
  CASE MAX(row_nbr) OVER () 
    WHEN total_rows THEN 'Y' 
    ELSE 'N' 
  END AS last_page, -- 是否最后一頁
  total_rows, -- 總記錄數
  row_nbr, -- 每一條數據的偏移量
  ((row_nbr - 1) / 10) + 1 AS current_page -- 當前所在頁碼
FROM t
ORDER BY emp_id;


-- MySQL、PostgreSQL、SQLite
WITH e AS ( -- 初始查詢
  SELECT emp_id, emp_name, sex, email
  FROM employee
),
t AS (
  SELECT emp_id, emp_name, sex, email, 
         COUNT(*) OVER () AS total_rows, -- 總記錄數
         ROW_NUMBER () OVER (ORDER BY e.emp_id) AS row_nbr -- 偏移量,ORDER BY和初始查詢相同
  FROM e
  ORDER BY e.emp_id -- 排序
  LIMIT 10
  OFFSET 10 ROWS -- 分頁
)
SELECT
  emp_id, emp_name, sex, email,
  COUNT(*) OVER () AS actual_page_size, -- 當前頁實際記錄數
  CASE MAX(row_nbr) OVER () 
    WHEN total_rows THEN 'Y' 
    ELSE 'N' 
  END AS last_page, -- 是否最后一頁
  total_rows, -- 總記錄數
  row_nbr, -- 每一條數據的偏移量
  ((row_nbr - 1) / 10) + 1 AS current_page -- 當前所在頁碼
FROM t
ORDER BY emp_id;

首先,我們定義了通用表表達式 e,它是返回數據的初始查詢,可以增加其他的過濾條件。

然后,我們基于 e 定義了另一個通用表表達式 t,在定義中進行了排序和分頁,并且利用窗口函數 COUNT(*) 計算總的記錄數,利用窗口函數 ROW_NUMBER () 計算每條數據的偏移量(行號)。

接下來,我們基于 t 返回了更多的參數,利用窗口函數 COUNT(*) 返回了當前頁的實際記錄數,通過窗口函數 MAX(row_nbr) 返回的當前頁最大偏移量和總記錄數的比較判斷是否最后一頁,以及當前所在的頁碼。

emp_id|emp_name|sex|email              |actual_page_size|last_page|total_rows|row_nbr|current_page|
------+--------+---+-------------------+----------------+---------+----------+-------+------------+
    11|關平    |男 |guanping@shuguo.com|              10|N        |        27|     11|           2|
    12|趙氏    |女 |zhaoshi@shuguo.com |              10|N        |        27|     12|           2|
    13|關興    |男 |guanxing@shuguo.com|              10|N        |        27|     13|           2|
    14|張苞    |男 |zhangbao@shuguo.com|              10|N        |        27|     14|           2|
    15|趙統    |男 |zhaotong@shuguo.com|              10|N        |        27|     15|           2|
    16|周倉    |男 |zhoucang@shuguo.com|              10|N        |        27|     16|           2|
    17|馬岱    |男 |madai@shuguo.com   |              10|N        |        27|     17|           2|
    18|法正    |男 |fazheng@shuguo.com |              10|N        |        27|     18|           2|
    19|龐統    |男 |pangtong@shuguo.com|              10|N        |        27|     19|           2|
    20|蔣琬    |男 |jiangwan@shuguo.com|              10|N        |        27|     20|           2|

總結

本文介紹了如何利用窗口函數在一個語句中返回分頁查詢的結果和所需的全部參數,這種方法比傳統的分頁查詢實現更加簡潔高效。

責任編輯:華軒 來源: SQL編程思想
相關推薦

2018-12-25 16:30:15

SQL Server高效分頁數據庫

2011-08-19 09:30:42

分頁查詢SQL ServerMySQL

2010-09-26 15:29:13

sql查詢分頁

2009-09-21 13:42:47

Hibernate查詢

2012-07-23 14:30:33

Oracle

2022-02-06 11:35:53

SQL數據函數

2010-05-13 16:21:58

2009-11-16 10:16:24

PHP文件上傳

2009-11-16 15:56:46

PHP數組查詢

2025-05-19 00:02:45

SQL窗口函數

2023-11-09 14:47:51

SQL工具數據庫

2010-09-13 13:19:16

Sql Server分

2010-05-25 15:12:22

MySQL分頁

2010-06-10 13:04:31

2009-11-06 09:23:41

ASP.NET高效分頁

2010-09-07 10:35:38

SQL語句

2024-05-23 10:19:57

2009-09-15 10:46:04

LINQ to SQL

2010-11-10 15:29:40

SQL SERVER

2020-11-11 14:36:57

ExcelSQL函數
點贊
收藏

51CTO技術棧公眾號

主站蜘蛛池模板: 国产精品毛片一区二区三区 | 天堂成人国产精品一区 | 欧美亚洲在线 | 午夜免费观看 | 日日操夜夜操天天操 | 91在线观看 | 日本三级网址 | 欧美视频一区 | 日一区二区| 欧美精品在线一区二区三区 | 99精品视频在线观看免费播放 | 日韩成人在线视频 | 激情欧美一区二区三区中文字幕 | 欧美一级黄色免费 | 亚洲精品一区二区 | 亚洲人一区| 国产农村妇女精品一区 | 久久国产精品久久国产精品 | 亚洲免费在线 | 毛片入口 | 一区二区三区在线免费观看 | 一区二区三区成人 | 天堂视频一区 | 精品中文字幕在线 | 福利精品 | 韩国理论电影在线 | 日韩欧美网 | 欧美中文字幕 | av免费观看网站 | 日韩日韩日韩日韩日韩日韩日韩 | 91精品在线播放 | 人人做人人澡人人爽欧美 | 看片wwwwwwwwwww| 午夜噜噜噜 | 亚洲精品福利在线 | 宅女噜噜66国产精品观看免费 | 亚洲精品久久久久中文字幕二区 | 国产欧美精品一区二区色综合 | 一级片av| 羞羞的视频免费观看 | 亚洲综合电影 |