我发现了这个：

Quote：

这是仅存在于SQL Server 2008中的解析器错误。2012年之前的非PDW版本的SQL Server不支持ORDER BY聚合函数的子句，如MIN

更多细节



你不要这里没有SQL 2008，但考虑到上面提到的，我想你可以试试这个：

 声明  @ Buckets   table （ ID  char （ 2 ），FullCapacity  int  ）
声明  @ Filler  表（ID  char （ 2 ），填充 int ）

 insert   into   @Buckets  值 
（'n>  B1'， 100 ），
（'  B2'， 50 ），
（'  B3'， 70 ）

插入 进入  @ Filler  值 
（'  F1'， 90 ），
（'  F2'， 70 ），
（'  F3'， 40 ），
（'  F4'， 20 ）

;  填写erCte  as  
（
选择 
 ID，
填充，
总和（填充程序） over （ order   by  ID） as  TotalFill 
来自  @ Filler  
），
 BucketCte  as  
（
选择 
 ID，
 FullCapacity，
 sum（FullCapacity） over （ order   by  ID） -  FullCapacity  as  RunningTotalCapacity 
 from   @ Buckets  
）
 select  
 b.ID  as  b_ID，
 f.ID  as  f_ID，
 b.FullCapacity，
 case  
  f.TotalFill< b.RunningTotalCapacity 然后  0  
何时 f.TotalFill> b.RunningTotalCapacity + b.FullCapacity 然后 b.FullCapacity 
 else  f.TotalFill  -  b。 RunningTotalCapacity 
 end   as  CurrentAmount 
进入 #tmp 
来自 fillerCte  as  f 
交叉  join  BucketCte  as  b 

 SELECT  f_ID  as  ID，FullCapacity，CurrentAmount 
 FROM  
 #tmp 
 ORDER   BY  f_ID，b_ID 

I have the below query which works fine in 2012 as it is using the 2012 features. Can anyone make the exact same query work in 2008 with same out put?

declare @Buckets table (ID char(2), FullCapacity int)
declare @Filler table (ID char(2), Filler int)

insert into @Buckets values
('B1', 100),
('B2', 50),
('B3', 70)

insert into @Filler values
('F1', 90),
('F2', 70),
('F3', 40),
('F4', 20)

;with fillerCte as
(
    select      
        ID,
        Filler,
        sum(Filler) over (order by ID) as TotalFill
    from @Filler
), 
BucketCte as
(
    select 
        ID,
        FullCapacity,
        sum(FullCapacity) over (order by ID) - FullCapacity as RunningTotalCapacity
    from @Buckets
)
select 
    b.ID, 
    b.FullCapacity,
    case 
        when f.TotalFill < b.RunningTotalCapacity then 0
        when f.TotalFill > b.RunningTotalCapacity + b.FullCapacity then b.FullCapacity
        else f.TotalFill - b.RunningTotalCapacity
    end as CurrentAmount
from fillerCte as f
cross join BucketCte as b
order by f.ID, b.ID

What I have tried:

declare @Buckets table (ID char(2), FullCapacity int)
declare @Filler table (ID char(2), Filler int)

insert into @Buckets values
('B1', 100),
('B2', 50),
('B3', 70)

insert into @Filler values
('F1', 90),
('F2', 70),
('F3', 40),
('F4', 20)

;with fillerCte as
(
    select      
        ID,
        Filler,
        sum(Filler) over (order by ID) as TotalFill
    from @Filler
), 
BucketCte as
(
    select 
        ID,
        FullCapacity,
        sum(FullCapacity) over (order by ID) - FullCapacity as RunningTotalCapacity
    from @Buckets
)
select 
    b.ID, 
    b.FullCapacity,
    case 
        when f.TotalFill < b.RunningTotalCapacity then 0
        when f.TotalFill > b.RunningTotalCapacity + b.FullCapacity then b.FullCapacity
        else f.TotalFill - b.RunningTotalCapacity
    end as CurrentAmount
from fillerCte as f
cross join BucketCte as b
order by f.ID, b.ID

I found this :

Quote:

This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MIN

more details

You don't have a SQL 2008 here, but considering the above mentioned, i think you can try this:

declare @Buckets table (ID char(2), FullCapacity int)
declare @Filler table (ID char(2), Filler int)
 
insert into @Buckets values
('B1', 100),
('B2', 50),
('B3', 70)
 
insert into @Filler values
('F1', 90),
('F2', 70),
('F3', 40),
('F4', 20)
 
;with fillerCte as
(
    select      
        ID,
        Filler,
        sum(Filler) over (order by ID) as TotalFill
    from @Filler
), 
BucketCte as
(
    select 
        ID,
        FullCapacity,
        sum(FullCapacity) over (order by ID) - FullCapacity as RunningTotalCapacity
    from @Buckets
)
select 
    b.ID as b_ID,
    f.ID as f_ID, 
    b.FullCapacity,
    case 
        when f.TotalFill < b.RunningTotalCapacity then 0
        when f.TotalFill > b.RunningTotalCapacity + b.FullCapacity then b.FullCapacity
        else f.TotalFill - b.RunningTotalCapacity
    end as CurrentAmount
    into #tmp
from fillerCte as f
cross join BucketCte as b

SELECT f_ID as ID, FullCapacity,CurrentAmount
FROM 
 #tmp
ORDER BY f_ID, b_ID