sql-server - 将 T-SQL 交叉应用转换为 Oracle

Question

我希望将使用交叉应用的 SQL Server (T-SQL) 查询转换为 Oracle 11g。 Oracle直到12g才支持Cross Apply，所以我必须找到一个解决方法。查询背后的想法是，对于每个 = 'Foobar' 的 Tab.Name，我需要找到具有按 Tab.Date 排序的相同 ID 的上一行名称。 (此表包含 1 个具有不同名称和日期的 ID 的多行)。

这是 T-SQL 代码:

SELECT DISTINCT t1.ID
                t1.Name, 
                t1.Date, 
                t2.Date as 'PreviousDate',
                t2.Name as 'PreviousName'
FROM  Tab t1 
               OUTER apply (SELECT TOP 1 t2.Date, 
                                         t2.Name 
                            FROM  Tab t2 
                            WHERE  t1.Id = t2.Id 

                            ORDER BY t2.Date DESC) t2 
WHERE  t1.Name = 'Foobar' )

从技术上讲，我能够使用 LEFT JOIN 和 LAG() 函数在 Oracle 中重新创建相同的功能:

SELECT DISTINCT t1.ID
            t1.Name, 
            t1.Date, 
            t2.PreviousDate as PreviousDate,
            t2.PreviousName as PreviousName
FROM   Tab t1  
          LEFT JOIN (
                SELECT ID,
                LAG(Name) OVER (PARTITION BY ID ORDER BY PreviousDate) as PreviousName,
                LAG(Date) OVER (PARTITION BY ID ORDER BY PreviousDate) as PreviousDate
                FROM Tab) t2 ON t2.ID = t1.ID 
WHERE  t1.Name = 'Foobar' 

问题在于执行 Oracle 查询的顺序。它将从 Tab 中拉回所有行，对它们进行排序(由于 LAG 函数)，然后在将其连接到主查询时使用 ON 语句过滤它们。该表有数百万条记录，因此对每个 ID 执行此操作是不可行的。基本上，我想更改子查询中的操作顺序，以仅拉回单个 ID 的行，对这些行进行排序以查找前一个行，然后将其连接起来。关于如何调整它有什么想法吗？

TL;博士SQL Server:过滤器、顺序、连接Oracle:订单、过滤器、连接

Answer 1

您可以使用 row_number() 查找每个 (id) 组的最新行:

select  *
from    tab t1
left join
        (
        select  row_number() over (
                    partition by id
                    order by Date desc) as rn
        ,       *
        from    t2
        ) t2
on      t1.id = t2.id
        and t2.rn = 1 -- Latest row per id