不能在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

这实际上是可能的，只需对代码进行一些修改。只需在外部CTE之前添加一个选择
注：在Teradata工作良好

with outertest as(
   SELECT * FROM (
       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 ) OUTER_CTE
   ) select count(*) from outertest