在其他RDBMS(不是SQL Server)中模拟OUTER APPLY [英] Analog of OUTER APPLY in other RDBMS (not SQL Server)

问题描述

我在工作中使用SQL Server，并且使用OUTER APPLY子句有一些不错的技巧，可帮助我避免重复代码.例如，如果我有一个这样的表:

I'm using SQL Server at work, and I have some nice tricks with OUTER APPLY clause which helps me do not repeat code. For example, if I have a table like this:

create table Transactions
(
    ID bigint identity(1, 1) primary key, [Date] datetime, Amount decimal(29, 2), Amount2 decimal(29, 2)
)

insert into Transactions ([Date], Amount, Amount2)
select getdate(), 100.00, null union all
select getdate(), 25.00, 75.00

并且我想从中选择数据，例如每个不为null的行都有一行，我可以这样查询:

and I want to select data from it such as I will have row for each not null amount, I can do query like this:

select
  T.ID,
  T.[Date],
  OA.Amount
from Transactions as T
  outer apply (
      select T.Amount as Amount union all
      select T.Amount2 as Amount
  ) as OA
where OA.Amount is not null

代替使用union:

select
  T.ID,
  T.[Date],
  T.Amount
from Transactions as T
where T.Amount is not null

union all

select
  T.ID,
  T.[Date],
  T.Amount2 as Amount
from Transactions as T
where T.Amount2 is not null

所以我想知道-其他RDBMS是否有这种可能性?

So I wonder - are other RDBMS have such a possibility?

SQL FIDDLE

推荐答案

在Oracle中，横向联接是笛卡尔联接，其结果集取决于行的值.尚未引入新的关键字( SQLFiddle ):

In Oracle a lateral join is a cartesian join with a result set that is dependent upon the values of the row. No new keyword has been introduced yet (SQLFiddle):

SQL> CREATE OR REPLACE TYPE number_nt AS TABLE OF NUMBER;
  2  /

Type created
SQL> SELECT t.id, t.dt, u.column_value amount
  2    FROM Transactions t
  3   CROSS JOIN TABLE(number_nt(t.amount, t.amount2)) u;

        ID DT                AMOUNT
---------- ----------- ------------
         1 05/06/2013           100
         1 05/06/2013  
         2 05/06/2013            25
         2 05/06/2013            75

Oracle似乎在内部使用LATERAL关键字内部

Oracle appears to use the LATERAL keyword internally though.

这篇关于在其他RDBMS(不是SQL Server)中模拟OUTER APPLY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！