多数据库联表排序SQL分页查询方案求优化建议

我整理了几种主流数据库在联表+排序场景下的分页SQL实现，示例都是取结果集的第2-3行数据，分享给大家，也想请教有没有更优的写法：

MySQL 5.1 #

SELECT outerO.Id, outerO.Value 
FROM MyTable outerO 
WHERE outerO.id IN ( 
    SELECT DISTINCT o.id 
    FROM MyTable o 
    JOIN LinkTable l ON l.Fk = o.Id 
    WHERE o.Value LIKE ? ESCAPE '!' 
) 
ORDER BY outerO.Value DESC, outerO.Id ASC 
LIMIT 2 OFFSET 1

这个写法最直接贴合需求，逻辑清晰明了。

Oracle 11 #

SELECT outerO.Id, outerO.Value 
FROM MyTable outerO 
WHERE outerO.id IN ( 
    SELECT id 
    FROM( 
        SELECT row_.id, rownum rownum_ 
        FROM ( 
            SELECT id 
            FROM ( 
                SELECT DISTINCT o.Value, o.Id 
                FROM MyTable o 
                JOIN LinkTable l ON l.Fk = o.Id 
                WHERE o.Value LIKE ? ESCAPE '!' 
                ORDER BY o.Value DESC, o.Id ASC 
            ) 
        ) row_ 
        WHERE rownum <= 3 
    ) 
    WHERE rownum_ > 1 
) 
ORDER BY outerO.Value DESC, outerO.Id ASC

相比MySQL，Oracle 11的实现要复杂得多，目前我还没找到子查询更少的实现方式。

DB2 10.5 #

SELECT outerO.Id, outerO.Value 
FROM MyTable outerO 
WHERE outerO.id IN ( 
    SELECT inner1_.id 
    FROM ( 
        SELECT inner2_.id, rownumber() OVER(ORDER BY ORDER OF inner2_) AS rownumber_ 
        FROM ( 
            SELECT id 
            FROM ( 
                SELECT DISTINCT o.Value, o.Id 
                FROM MyTable o 
                JOIN LinkTable l ON l.Fk = o.Id 
                WHERE o.Value LIKE ? ESCAPE '!' 
                ORDER BY o.Value DESC, o.Id ASC 
            ) FETCH FIRST 3 ROWS ONLY 
        ) AS inner2_ 
    ) AS inner1_ 
    WHERE inner1_.rownumber_ > 1 
    ORDER BY inner1_.rownumber_ 
) 
ORDER BY outerO.Value DESC, outerO.Id ASC

DB2的实现层级也比较多，我同样没找到子查询更少的写法。

Microsoft SQL-Server 2016 #

SELECT outerO.Id, outerO.Value 
FROM MyTable outerO 
WHERE outerO.id IN ( 
    SELECT DISTINCT o.id 
    FROM MyTable o 
    JOIN LinkTable l ON l.Fk = o.Id 
    WHERE o.Value LIKE ? ESCAPE '!' 
) 
ORDER BY outerO.Value DESC, outerO.Id ASC 
OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY

这个语法和MySQL类似，用OFFSET FETCH就很简洁。

以上这些查询都经过验证可以正常运行，想问问各位技术同仁有没有更高效、简洁的实现方案，欢迎赐教！

内容的提问来源于stack exchange，提问作者tangens