问题描述
是否可以在 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