文章目錄
- 測驗資料
- PostgreSQL 12 之前的實作
- PostgreSQL 13 的增強功能
- 注意事項
- 總結
大家好，我是只談技術不剪發的 Tony 老師，
PostgreSQL 全球開發組于 2020 年 9 月 24 日宣布 PostgreSQL 13 正式發布，接下來我會通過一系列文章為大家決議這個新版本帶來的功能增強，本文先給大家介紹一個與 SQL 開發相關的新特性：FETCH FIRST 子句增加了 WITH TIES 選項，可以用于回傳更多排名相同的資料行，
如果覺得文章有用，歡迎關注??、評論📝、點贊👍
測驗資料
首先，我們創建一個簡單的測驗表 t 并插入一些資料：
CREATE TABLE t(name varchar(20), score int);
INSERT INTO t VALUES('張三', 80), ('李四', 77), ('王五', 80), ('趙六', 90), ('孫七', 80);
SELECT * FROM t;
name |score|
-----|-----|
張三 | 80|
李四 | 77|
王五 | 80|
趙六 | 90|
孫七 | 80|
該表中只包含姓名和分數 2 個欄位，其中有 3 個學生的分數相同，
PostgreSQL 12 之前的實作
PostgreSQL 12 以及之前的版本實作了 SQL 標準中的 FETCH 子句：
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY
其中，OFFSET 表示跳過指定的 start 行數， 默認為 0；FETCH 表示最多回傳 count 行結果，默認為 1；ROW 和 ROWS 是同義詞，FIRST 和 NEXT 是同義詞；ONLY 表示不回傳更多的資料，
例如，以下陳述句回傳了分數最高的 2 個學生：
SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS ONLY;
name |score|
-----|-----|
趙六 | 90|
張三 | 80|
雖然的確回傳了分數最高的兩條記錄，但是從表中的資料可以看出分數為 80 的還有兩名同學，如果計算排名的話他們也應該是第二名，
另外需要注意，如果沒有指定 ORDER BY 子句，查詢將會回傳隨機的 2 行記錄：
SELECT *
FROM t
FETCH FIRST 2 ROWS ONLY;
name |score|
-----|-----|
張三 | 80|
李四 | 77|
📝PostgreSQL 還支持另一種限定查詢結果數量的子句：
LIMIT { count | ALL } OFFSET start，也可以實作相同的功能，
PostgreSQL 13 的增強功能
為了解決回傳排名相同的資料問題，PostgreSQL 13 對 FETCH FIRST 子句進行了增強：
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
其中，WITH TIES 選項表示回傳排名和最后一行相同的額外資料行，而且必須同時指定 ORDER BY 子句，例如，以下陳述句回傳了分數排名前 2 的所有學生：
SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS WITH TIES;
name |score|
-----|-----|
趙六 | 90|
張三 | 80|
王五 | 80|
孫七 | 80|
如果使用 WITH TIES 選項時沒有指定 ORDER BY 子句，將會回傳錯誤：
SELECT *
FROM t
FETCH FIRST 2 ROWS WITH TIES;
RROR: WITH TIES cannot be specified without ORDER BY clause
注意事項
使用 WITH TIES 選項時需要注意一點，FETCH FIRST 陳述句回傳的結果數量不再是確定值，例如在上面的陳述句中，雖然指定了 count 為 2，但實際回傳了 4 行資料，如果我們基于這個功能實作分頁查詢，需要記錄每次實際回傳的行數；對應上面的示例，第一頁回傳 4 行資料，查詢第二頁時的陳述句如下：
SELECT *
FROM t
ORDER BY score
OFFSET 4
FETCH FIRST 2 ROWS WITH TIES;
其中，OFFSET 4 是上一頁實際回傳的行數，
另外，這種情況下的 ORDER BY 子句中不能出現其他用于排序顯示的欄位，假如我們想要找出分數前 2 名的所有學生，同時對于分數相同的學生按照姓名拼音進行排序：
SELECT *
FROM t
ORDER BY score DESC, name COLLATE "zh_CN"
FETCH FIRST 2 ROWS WITH TIES;
name |score|
-----|-----|
趙六 | 90|
孫七 | 80|
由于 name 欄位出現在 ORDER BY 中，其他分數為 80 的學生不再被認為是排名相同的資料，除非他們的姓名也叫做“孫七”，為了解決這個問題，可以增加一個子查詢或者使用通用表運算式（WITH 子句）：
WITH d AS (
SELECT *
FROM t
ORDER BY score DESC
FETCH FIRST 2 ROWS WITH TIES
)
SELECT *
FROM d
ORDER BY score DESC, name COLLATE "zh_CN";
name |score|
-----|-----|
趙六 | 90|
孫七 | 80|
王五 | 80|
張三 | 80|
其中，第一個 ORDER BY 用于分數排名，第二個 ORDER BY 用于最終的排序顯示，
📝按照 SQL 標準，除了可以使用 count 限定回傳的行數之外，還可以使用 N PERCENT 按照百分比限定回傳的行數，PostgreSQL 目前沒有實作這一功能，
總結
作為世界上最先進的開源資料庫，PostgreSQL 新版本增加了 FETCH FIRST 子句對于 WITH TIES 的支持，為應用程式開發提供了更多的便利，關于 PostgreSQL 13 新特性的更多內容，可以參考這篇文章，
CSDN認證博客專家
資料庫架構師
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/153013.html
標籤：其他