向您的SQL工具链添加横向联接或交叉应用

最新推荐文章于 2024-04-21 21:48:48 发布

danpu0978

最新推荐文章于 2024-04-21 21:48:48 发布

阅读量187

点赞数

文章标签： mysql sql 数据库 java oracle

T-SQL语言早已知道强大的CROSS APPLY和OUTER APPLY JOIN语法。 SQL：1999标准引入了几乎等效的“横向派生表”，最终由PostgreSQL 9.3或Oracle 12c支持，后者同时采用了SQL标准LATERAL语法和T-SQL供应商特定的CROSS APPLY和OUTER APPLY语法。

但是我们什至在说什么呢？

SQL功能具有其他语言所没有的独特特征。对于不了解它们的人来说，它们是晦涩的，因为每种语言功能都会引入带有新关键字的新语法。在这种情况下： APPLY和LATERAL 。但这并不难理解。使用CROSS APPLY所做的全部工作就是两个表之间的CROSS JOIN ，其中CROSS JOIN表达式的右侧可以引用CROSS JOIN表达式左侧的列。考虑一下Martin Smith关于Stack Overflow的以下示例：

重用列别名

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (
  SELECT 2 * CAST(number AS BIGINT)
) CA1(doubled_number)
CROSS APPLY (
  SELECT doubled_number + 1
) CA2(doubled_number_plus_one)

_{请参阅上面示例的SQLFiddle}

在此示例中，我们从系统表中选择数字，然后交叉应用标量子选择，将每个数字乘以2。然后，对整个表格产品，我们交叉应用另一个标量子选择，将最后一个数字加一个。

此特定示例也可以使用SELECT子句中的子查询来实现。但是，如您在上面的示例中看到的那样，可以doubled_number_plus_one从先前计算的列中计算出doubled_number_plus_one 。子查询并不是那么“简单”。

将表值函数应用于每个记录

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

当您要将表值函数连接到另一个表的每个记录时，此示例可能会更加有用。

PostgreSQL的LATERAL衍生表格

在PostgreSQL中，可以通过将表值函数放在SELECT子句中来神奇地完成此操作：

SELECT x, GENERATE_SERIES(0, x)
FROM (VALUES(0), (1), (2)) t(x)

_{请参阅上面示例的SQLFiddle}

以上收益

| X | GENERATE_SERIES |
|---|-----------------|
| 0 |               0 |
| 1 |               0 |
| 1 |               1 |
| 2 |               0 |
| 2 |               1 |
| 2 |               2 |

另外，从PostgreSQL 9.3开始，您可以这样使用显式的横向派生表：

SELECT x, y
FROM (VALUES(0), (1), (2)) t(x),
LATERAL GENERATE_SERIES(0, t.x) u(y)

_{请参阅上面示例的SQLFiddle}

再次屈服

| X | Y |
|---|---|
| 0 | 0 |
| 1 | 0 |
| 1 | 1 |
| 2 | 0 |
| 2 | 1 |
| 2 | 2 |

在jOOQ 3.3中交叉应用和外部应用

即将发布的jOOQ 3.3版本也将支持上述条款，您可以在此处编写如下查询：

DSL.using(configuration)
   .select()
   .from(AUTHOR)
   .crossApply(
        select(count().as("c"))
       .from(BOOK)
       .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)))
   .fetch();

或横向连接：

DSL.using(configuration)
   .select()
   .from(
        values(row(0), row(1), row(2))
            .as("t", "x"),
        lateral(generateSeries(0,
                fieldByName("t", "x"))
            .as("u", "y")))
   .fetch();

无论您使用的是jOOQ还是本机SQL，横向派生表或CROSS APPLY都绝对应该成为您超凡的SQL工具链的一部分！

参考：在JAVA，SQL和JOOQ博客上，可以从我们的JCG合作伙伴 Lukas Eder 向您的SQL工具链添加横向联接或交叉应用。