db2分頁sql

我敢肯定，到目前為止，您已經以多種方式弄錯了。 而且您可能很快將無法正確處理。 那么，當您可以實施業務邏輯時，為什么還要在SQL調整上浪費您的寶貴時間呢？

讓我解釋…

直到最近的SQL：2008標準 ，MySQL用戶才知道的LIMIT .. OFFSET被標準化為以下簡單語句：

SELECT * FROM BOOK OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

是。 關鍵字太多了。

SQL確實是一種非常冗長的語言。 就個人而言，我們真的很喜歡MySQL / PostgreSQLLIMIT .. OFFSET子句的簡潔性，這就是為什么我們為jOOQ DSL API選擇它的原因 。

在SQL中：

SELECT * FROM BOOK LIMIT 1 OFFSET 2

在jOOQ中：

select().from(BOOK).limit(1).offset(2);

現在，當您是SQL框架供應商時，或者在滾動自己的內部SQL抽象時，您可能會考慮標準化此簡潔的小子句。 這是數據庫中固有支持偏移分頁的兩種口味：

-- MySQL, H2, HSQLDB, Postgres, and SQLite SELECT * FROM BOOK LIMIT 1 OFFSET 2-- CUBRID supports a MySQL variant of the -- LIMIT .. OFFSET clause SELECT * FROM BOOK LIMIT 2, 1-- Derby, SQL Server 2012, Oracle 12, SQL:2008 SELECT * FROM BOOK OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY-- Ingres. Eek, almost the standard. Almost! SELECT * FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY-- Firebird SELECT * FROM BOOK ROWS 2 TO 3-- Sybase SQL Anywhere SELECT TOP 1 ROWS START AT 3 * FROM BOOK-- DB2 (without OFFSET) SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY-- Sybase ASE, SQL Server 2008 (without OFFSET) SELECT TOP 1 * FROM BOOK

到目前為止，一切都很好。 這些都可以處理。 一些數據庫將偏移量放在限制之前，另一些數據庫則將限制放在偏移量之前，并且T-SQL系列將整個TOP子句放在SELECT列表之前。 這很容易模仿。 現在呢：

• Oracle 11g及以下
• SQL Server 2008及更低版本
• 具有偏移量的DB2

（ 請注意，您可以在DB2中啟用各種替代語法 ）

當您使用google搜索時，您會發現數百萬種方法可以在那些較舊的數據庫中模擬OFFSET .. FETCH 。 最佳解決方案始終涉及：

• 在Oracle中使用帶有ROWNUM篩選的雙嵌套派生表
• 在SQL Server和DB2中使用帶有ROW_NUMBER()篩選的單嵌套派生表格

因此，您正在模仿它。

您認為您會做對嗎？

讓我們來解決一些您可能沒有想到的問題。

首先，Oracle。 Oracle顯然想創建一個最大的供應商鎖定，只有蘋果最近推出了Swift才超過了。 這就是為什么ROWNUM解決方案的性能最佳，甚至優于基于SQL：2003標準窗口函數的解決方案的原因。 不相信嗎？ 閱讀有關Oracle偏移分頁性能的這篇非常有趣的文章 。

因此，Oracle中的最佳解決方案是：

-- PostgreSQL syntax: SELECT ID, TITLE FROM BOOK LIMIT 1 OFFSET 2-- Oracle equivalent: SELECT * FROM (SELECT b.*, ROWNUM rnFROM (SELECT ID, TITLEFROM BOOK) bWHERE ROWNUM <= 3 -- (1 + 2) ) WHERE rn > 2

那真的等效嗎？

當然不是。 您正在選擇其他列，即rn列。 在大多數情況下，您可能并不在意，但是如果您想進行有限的子查詢以與IN謂詞一起使用怎么辦？

-- PostgreSQL syntax: SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT IDFROM AUTHORLIMIT 1 OFFSET 2 )-- Oracle equivalent: SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT * -- Ouch. These are two columns!FROM (SELECT b.*, ROWNUM rnFROM (SELECT IDFROM AUTHOR) bWHERE ROWNUM <= 3)WHERE rn > 2 )

因此，如您所見，您將不得不執行一些更復雜SQL轉換。 如果您要手動模擬LIMIT .. OFFSET ，則可以將ID列修補到子查詢中：

SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT ID -- betterFROM (SELECT b.ID, ROWNUM rn -- betterFROM (SELECT IDFROM AUTHOR) bWHERE ROWNUM <= 3)WHERE rn > 2 )

所以，更像是吧？ 但是，由于您不是每次都手動編寫此代碼，因此您將開始創建自己的漂亮的內部SQL框架，該框架涵蓋到目前為止所遇到的2-3個用例，對嗎？

你能行的。 因此，您將自動regex-search-replace列名以產生上述內容。

所以現在，對嗎？

當然不是！ 因為您可以在頂級SELECT包含不明確的列名，但不能在嵌套選擇中包含。 如果要這樣做：

-- PostgreSQL syntax: -- Perfectly valid repetition of two ID columns SELECT BOOK.ID, AUTHOR.ID FROM BOOK JOIN AUTHOR ON BOOK.AUTHOR_ID = AUTHOR.ID LIMIT 1 OFFSET 2-- Oracle equivalent: SELECT * FROM (SELECT b.*, ROWNUM rnFROM (-- Ouch! ORA-00918: column ambiguously definedSELECT BOOK.ID, AUTHOR.IDFROM BOOKJOIN AUTHORON BOOK.AUTHOR_ID = AUTHOR.ID) bWHERE ROWNUM <= 3 ) WHERE rn > 2

不。 而且，由于您有多個ID實例，因此手動修補前面示例中的ID列的技巧不起作用。 并且將列重命名為隨機值是很麻煩的，因為您自己的內部數據庫框架的用戶希望接收定義良好的列名稱。 即ID和… ID 。

因此，解決方案是將列重命名兩次。 在每個派生表中一次：

-- Oracle equivalent: -- Rename synthetic column names back to original SELECT c1 ID, c2 ID FROM (SELECT b.c1, b.c2, ROWNUM rnFROM (-- synthetic column names hereSELECT BOOK.ID c1, AUTHOR.ID c2FROM BOOKJOIN AUTHORON BOOK.AUTHOR_ID = AUTHOR.ID) bWHERE ROWNUM <= 3 ) WHERE rn > 2

但是現在，我們完成了嗎？

當然不是！ 如果您將這樣的查詢加倍嵌套怎么辦？ 您是否考慮將ID列重命名為合成名稱，然后再重新命名？ ……讓我們留在這里，談論完全不同的事情：

SQL Server 2008是否可以使用相同的功能？

當然不是！ 在SQL Server 2008中，最流行的方法是使用窗口函數。 即ROW_NUMBER() 。 因此，讓我們考慮：

-- PostgreSQL syntax: SELECT ID, TITLE FROM BOOK LIMIT 1 OFFSET 2-- SQL Server equivalent: SELECT b.* FROM (SELECT ID, TITLE, ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

就這樣吧？

當然不是！

好的，我們已經遇到了這個問題。 我們不應該選擇* ，因為在我們將其用作IN謂詞的子查詢的情況下，這會生成過多的列。 因此，讓我們考慮使用綜合列名稱的正確解決方案：

-- SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT ID c1, TITLE c2,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

但是現在我們明白了，對不對？

做出有根據的猜測： 不！

如果您在原始查詢中添加ORDER BY子句，會發生什么情況？

-- PostgreSQL syntax: SELECT ID, TITLE FROM BOOK ORDER BY SOME_COLUMN LIMIT 1 OFFSET 2-- Naive SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT ID c1, TITLE c2,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOKORDER BY SOME_COLUMN ) b WHERE rn > 2 AND rn <= 3

現在，這在SQL Server中不起作用。 子查詢不允許具有ORDER BY子句，除非它們也具有TOP子句（或SQL Server 2012中的OFFSET .. FETCH子句）。

好的，我們可以使用TOP 100 PERCENT進行調整，以使SQL Server滿意。

-- Better SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT TOP 100 PERCENTID c1, TITLE c2,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOKORDER BY SOME_COLUMN ) b WHERE rn > 2 AND rn <= 3

現在，根據SQL Server，這是正確SQL，盡管您不能保證在查詢執行后派生表的順序將繼續存在。 很可能是由于某種影響再次改變了順序。

如果要在外部查詢中按SOME_COLUMN進行排序， SOME_COLUMN必須再次轉換SQL語句以添加另一個綜合列：

-- Better SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT TOP 100 PERCENTID c1, TITLE c2,SOME_COLUMN c99,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3 ORDER BY b.c99

確實開始變得有點討厭。 讓我們猜一下是否：

這是正確的解決方案！

當然不是！ 如果原始查詢中包含DISTINCT怎么辦？

-- PostgreSQL syntax: SELECT DISTINCT AUTHOR_ID FROM BOOK LIMIT 1 OFFSET 2-- Naive SQL Server equivalent: SELECT b.c1 AUTHOR_ID FROM (SELECT DISTINCT AUTHOR_ID c1,ROW_NUMBER() OVER (ORDER BY AUTHOR_ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

現在，如果一位作家寫了幾本書怎么辦？ 是的， DISTINCT關鍵字應該刪除這些重復項，并且有效地，PostgreSQL查詢將首先正確刪除重復項，然后應用LIMIT和OFFSET 。

但是， ROW_NUMBER()謂詞在 DISTINCT可以再次刪除它們之前總是生成不同的行號。 換句話說， DISTINCT無效。

幸運的是，我們可以使用以下巧妙的小技巧再次調整此SQL ：

-- Better SQL Server equivalent: SELECT b.c1 AUTHOR_ID FROM (SELECT DISTINCT AUTHOR_ID c1,DENSE_RANK() OVER (ORDER BY AUTHOR_ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

在此處閱讀有關此技巧的更多信息：

SQL技巧：row_number（）是SELECT，而density_rank（）是SELECT DISTINCT 。

請注意， ORDER BY子句必須包含SELECT字段列表中的所有列。 顯然，這會將SELECT DISTINCT字段列表中可接受的列限制為窗口函數的ORDER BY子句中允許的列（例如，沒有其他窗口函數）。

我們當然也可以嘗試使用通用表表達式來解決此問題，或者我們考慮

另一個問題？

當然是！

您甚至不知道窗口函數的ORDER BY子句中的列應該是什么？ 您是否剛剛隨機選擇了任何一欄？ 如果該列上沒有索引該怎么辦，您的窗口函數仍會執行嗎？

當原始的SELECT語句還具有ORDER BY子句時，答案很容易，那么您應該采用該子句（如果適用，還要加上SELECT DISTINCT子句中的所有列）。

但是，如果您沒有任何ORDER BY子句怎么辦？

還有另一把戲！ 使用“常量”變量：

-- Better SQL Server equivalent: SELECT b.c1 AUTHOR_ID FROM (SELECT AUTHOR_ID c1,ROW_NUMBER() OVER (ORDER BY @@version) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

是的，您需要使用一個變量，因為在SQL Server的那些ORDER BY子句中不允許使用常量。 痛苦的，我知道。

在此處閱讀有關此@@ version技巧的更多信息 。

我們完成了嗎？

可能不是！ 但是，我們可能已經涵蓋了大約99％的常見案例和邊緣案例。 現在，我們可以睡個好覺了。

注意，所有這些SQL轉換都是在jOOQ中實現的。 jOOQ是唯一認真對待SQL（帶有所有缺點和警告）SQL抽象框架，它對所有這些瘋狂行為進行了標準化。

如開頭所述，使用jOOQ，您只需編寫：

// Don't worry about general emulation select().from(BOOK).limit(1).offset(2);// Don't worry about duplicate column names // in subselects select(BOOK.ID, AUTHOR.ID) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)) .limit(1).offset(2);// Don't worry about invalid IN predicates select() .from(BOOK) .where(BOOK.AUTHOR_ID).in(select(AUTHOR.ID).from(AUTHOR).limit(1).offset(2) );// Don't worry about the ROW_NUMBER() vs. // DENSE_RANK() distinction selectDistinct(AUTHOR_ID).from(BOOK).limit(1).offset(2);

使用jOOQ，您可以像編寫PostgreSQL一樣出色地編寫Oracle SQL或Transact SQL！ …不必完全跳起SQL船 ，而是繼續使用JPA。

鍵集分頁

現在，當然，如果您正在閱讀我們的博客或我們的合作伙伴博客SQL Performance Explained ，那么現在您應該知道， OFFSET分頁通常首先是一個不好的選擇。 您應該知道，鍵集分頁幾乎總是優于OFFSET分頁。

在此處，了解jOOQ如何使用SEEK子句原生支持鍵集分頁 。

翻譯自: https://www.javacodegeeks.com/2014/06/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework.html

db2分頁sql

總結

以上是生活随笔為你收集整理的db2分页sql_停止尝试使用内部DB框架模拟SQL OFFSET分页！的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。