CTE 中的 CTE

时间:2022-10-14
本文介绍了CTE 中的 CTE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

是否可以在 CTE 中编写 CTE?

Is it possible to write a CTE within a CTE?

我希望它遵循这个逻辑,但解释器不喜欢这段代码.

I want it to follow this logic, but the interpreter doesn't like this code.

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

推荐答案

您不能在 SQL Server 中嵌套这样的 CTE,但您可以通过以下方式使用多个 CTE:

You can't nest CTEs like that in SQL Server but you can use multiple CTEs the following way:

;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

这篇关于CTE 中的 CTE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

上一条:如何在SQL表中插入空行? 下一条:如何获得对 SQL Server 2005 数据库的独占访问权限来恢复?

相关文章

最新文章