企业网站制作公司合肥,微商手机网站制作公司,服务app开发的公司,网站识别手机电脑自动跳转我敢肯定#xff0c;到目前为止#xff0c;您已经以多种方式弄错了。 而且您可能很快将无法正确处理。 那么#xff0c;当您可以实施业务逻辑时#xff0c;为什么还要在SQL调整上浪费您的宝贵时间呢#xff1f; 让我解释… 直到最近的SQL#xff1a;2008标准 #xff0… 我敢肯定到目前为止您已经以多种方式弄错了。 而且您可能很快将无法正确处理。 那么当您可以实施业务逻辑时为什么还要在SQL调整上浪费您的宝贵时间呢 让我解释… 直到最近的SQL2008标准 MySQL用户才知道的LIMIT .. OFFSET被标准化为以下简单语句 SELECT *
FROM BOOK
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY 是。 关键字太多了。 SQL确实是一种非常冗长的语言。 就个人而言我们真的很喜欢MySQL / PostgreSQL的LIMIT .. 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。 甲骨文显然想创建一个最大的供应商锁定只有苹果公司最近推出了Swift才超过了。 这就是为什么ROWNUM解决方案的性能最佳甚至优于基于SQL2003标准窗口函数的解决方案的原因。 不相信吗 阅读有关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包含不明确的列名但不能在嵌套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您只需编写 // Dont worry about general emulation
select().from(BOOK).limit(1).offset(2);// Dont 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);// Dont worry about invalid IN predicates
select()
.from(BOOK)
.where(BOOK.AUTHOR_ID).in(select(AUTHOR.ID).from(AUTHOR).limit(1).offset(2)
);// Dont 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
