with cte1 as (
    select isnull(A,'Unknown') as A,isnull(nullif(B,'NULL'),'Unknown') as B,C
    from   ... -- uses collate sql_Latin1_General_CP1_CI_AS when joining 
    group by isnull(A,'Unknown'),C
    ),cte2 as (select top (2147483647) A,B,C from cte1 order by A,C),-- Removing cte2 makes it work if running directly as sql query. However,-- it still behave the same if the code is in view or table function 
    ctes as (
    .... -- pretty complex query joining cte2 multiple times
         -- uses row_number(),ntile
    )
    select count(*) from finalCTE

结果(计数)每次执行时都会更改.而且它远远小于它应该是多少.我发现以下任一步骤都可以使其正确.

>物化(临时或永久表)CTE cte1,并使用物化表.
>将cte1中的组更改为以下任何形式.

> group by A,’NULL’),’Unknown’),C
> group by isnull(A,nullif(B,C
> group by A,C
>在其他CTE中使用cte1代替cte2. (更新：此步骤并不总是起作用,尽管它在表函数中仍然存在问题,尽管它可以直接运行sql)

但是,为什么原始查询行为奇怪？这是sql Server中的错误吗？

全功能代码：

ALTER function [dbo].[fn] (@para1 char(3))
returns table
return
with    cte1 as ( select AAA,BBB,CCC
               from     dbo.fnBBB(12)
               where    @para1 = 'xxxx'
               union all
               select   AAA,CCC
               from     dbo.fnBBB2(12)
               where    @para1 = 'yyyy'
             ),-- Tested not using cte2,the same behave
        cte2 as (select top (2147483647) AAA,CCC from cte1 order by AAA,CCC),t as ( select   e.CCC,e.value1,cte2.BBB,cte2.AAA
               from     dbo.T1 e
                        join cte2 on e.CCC = cte2.CCC
             ),b as ( select   BBB,AAA,count(*) count,case when count(*) / 5 > 10 then 10 
                             else count(*) / 5 
                        end as buckets
               from     t 
               group by BBB,AAA 
               having   count(*) >= 5 
             ),b2
          as ( select   t.*
               from     b
                        cross apply ( select    *,ntile(b.buckets) over ( partition by t.BBB,t.AAA order by value1,CCC )
                                                as bucket
                                      from      t
                                      where     BBB = b.BBB
                                                and AAA = b.AAA
                                    ) t
             ),m1
          as ( select   AAA,b2.CCC,Date,SId,value2,b2.bucket,--
                        _asc = row_number() over ( partition by BBB,bucket,SId order by value2,b2.CCC ),_desc = row_number() over ( partition by BBB,SId order by value2 desc,b2.CCC desc ),count(*) over (partition by BBB,SId) scount
               from     b2 join dbo.T2 e on b2.CCC = e.CCC
             ),median
          as ( select   BBB,avg(value2) value2Median,min(scount) sCount
               from     m1
               where    _asc in ( _desc,_desc - 1,_desc + 1 )
               group by BBB,SId
             ),bounds
          as ( select   BBB,min(value1) dboMin,max(value1) value1Max,count(*) count 
               from     b2
               group by BBB,bucket 
             )
    select  m.*,b.dboMin,b.value1Max,Count
    from    median m join bounds b on m.BBB = b.BBB and m.AAA = b.AAA and m.bucket = b.bucket 
    -- order by BBB,bucket

cte1中使用的功能：

CREATE function [dbo].[fnBBB](@param int) 
returns table
return
with    m as ( select   * -- only this view has non default collate (..._CS_AS)
               from     dbo.view1 -- indxed view. 
             )
    select  isnull(g.AAA,'Unknown') as AAA,isnull(nullif(m1.value,'Unknown') as BBB,m.CCC
    from    m 
            left join dbo.mapping m0 on m0.id = 12
                and m0.value = m. v1 collate sql_Latin1_General_CP1_CI_AS
            left join dbo.map1 r on r.Country = m0.value
            left join dbo.map2 g on g.N = r.N
            left join dbo.mapping m1 on m1.id = 20
                and m1.value = m.v2 collate sql_Latin1_General_CP1_CI_AS
    where   m.run_date > dateadd(mm,-@param,getdate())
    group by isnull(g.AAA,m.CCC