CTE中的CTE

小编典典

CTE中的CTE

sql

是否可以在CTE中编写CTE？

我希望它遵循此逻辑，但是解释器不喜欢此代码。

with outertest as(

    with test as (
        select 
            SRnum, 
            gamenumber, 
            StartOfDistribution, 
            ApplicationNumber   
        from #main
        where startofdistribution = '2011-06-14 00:00:00.000'
        and SRnum = '313'
        --order by SRnum, gamenumber, StartOfDistribution, ApplicationNumber
    )
    select
        ApplicationNumber
        ,count(*) as RetailerAppearance
    from test
    group by ApplicationNumber
    having count(*) = 4

) select count(*) from outertest

阅读 316

2021-04-15

共1个答案

小编典典

您不能在SQL Server中像这样嵌套CTE，但是可以通过以下方式使用多个CTE：

;with test as 
(
    select 
        SRnum, 
        gamenumber, 
        StartOfDistribution, 
        ApplicationNumber   
    from #main
    where startofdistribution = '2011-06-14 00:00:00.000'
    and SRnum = '313'
    --order by SRnum, gamenumber, StartOfDistribution, ApplicationNumber
),
 outertest as
 (
    select
        ApplicationNumber
        ,count(*) as RetailerAppearance
    from test
    group by ApplicationNumber
    having count(*) = 4
) 
select count(*) 
from outertest

2021-04-15