将SQL Server CTE转换为Oracle CTE [英] Convert SQL Server CTE into Oracle CTE

问题描述

这是SQL Server CTE，试图转换为Oracle CTE或常规的oracle查询.

Here is the SQL Server CTE, trying to convert to Oracle CTE or regular oracle query..

;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;

推荐答案

假定您在cte/cte3选择列表中没有故意反转m和y列，我认为您可以将查询重写为: >

Assuming you didn't have the m and y columns reversed on purpose in your cte/cte3 select lists, I think you could rewrite your query as:

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);

您没有提供任何示例数据，因此我无法进行测试，但是值得将date函数转换为其等效的SQL Server，并进行测试以确保返回的数据相同.

You haven't provided any sample data, so I can't test, but it would be worth converting the date functions to their SQL Server equivalents and testing to make sure the data returned is the same.

这样，您不会查询同一张表3次，这应该会提高性能.

This way, you're not querying the same table 3 times, which should improve the performance some.

这篇关于将SQL Server CTE转换为Oracle CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！