将SQL Server CTE转换为Oracle CTE

Question

下面是SQL Server CTE，试图转换为Oracle CTE或常规oracle查询。

;with cte as 

(Select AC, M, Y, D, E, F, CD 

from tblA
WHere 

(Y = YEAR(GETDATE()) and M = Month(dateadd(month, -1, GETDATE()))) 

), 

cte2 as

(Select A.AC,Max(A.Y)as Y, Max(A.M) as M, Max(A.CD) as CD

from tbl A

Inner join cte B on B.AC = A.AC

WHere A.CD is Not Null and B.CD is Null

Group by A.AC)

, cte3 as

(Select C.AC, C.Y, C.M, C.D, C.E, C.F, C.CD

from tblA C

Inner join cte2 D on C.AC = D.AC and C.Y= D.Y and C.M = D.M and 

    D.CD = C.CD
) 
select * from cte

union

select * from cte3;

Answer 1

假设您没有在cte/cte 3选择列表中有意反转m和y列，我认为您可以将查询重写为：

with cte1 as (select a.ac,
                     a.m,
                     a.y,
                     a.d,
                     a.e,
                     a.f,
                     a.cd,
                     max(case when a.cd is not null and b.cd is not null then a.y end) over (partition by a.ac) max_y,
                     max(case when a.cd is not null and b.cd is not null then a.m end) over (partition by a.ac) max_m,
                     max(case when a.cd is not null and b.cd is not null then a.cd end) over (partition by a.ac) max_cd
              from   tbla a
                     left outer join tblb b on (a.ac = b.ac))
select ac,
       m,
       y,
       d,
       e,
       f,
       cd
from   cte1
where  (y = to_char(sysdate, 'yyyy')
        and m = to_char(add_months(sysdate, -1), 'mm'))
or     (y = max_y
        and m = max_m
        and cd = max_cd);

您没有提供任何示例数据，所以我无法进行测试，但值得将日期函数转换为它们的Server等效项并进行测试，以确保返回的数据是相同的。

这样，您就不会在同一个表中查询3次了，这会提高性能。