将SQL Server 2012 CTE转换为SQL Server 2008

debugcn 发表于 Dev

用户名

我有以下查询，由于使用了2012年的功能，因此在2012年可以正常使用。任何人都可以在2008年使用完全相同的输出来执行完全相同的查询吗？我正在寻找从Filler表到Bucket表的Bucket填充数据。在SQL 2008中执行它时出现错误。The Parallel Data Warehouse (PDW) features are not enabled

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

输出表应在下面显示填充过程。

ID | FullCapacity | CurrentAmount       
---+--------------+--------------
B1 |    100       |    90        
B2 |     50       |     0    
B3 |     70       |     0
---+--------------+--------------
B1 |    100       |   100        
B2 |     50       |    50
B3 |     70       |    10
---+--------------+--------------
B1 |    100       |   100      
B2 |     50       |    50    
B3 |     70       |    50
---+--------------+--------------
B1 |    100       |   100        
B2 |     50       |    50    
B3 |     70       |    70

马哈茂德·加马尔（Mahmoud Gamal）

要在SQL Server 2008中执行相同的查询，您必须分两步计算运行总计，第一步是使用来对行进行排名ROW_NUMBER() OVER(ORDER BY ID)，然后使用行号来计算运行总计：

;WITH fillerCte as
(
  SELECT 
   ID,
   Filler,
   ROW_NUMBER() OVER(ORDER BY Id) AS RN
   FROM @Filler
), FillerRT
AS
(
  SELECT 
    f1.ID,
    f1.Filler,
    SUM(f2.Filler) AS TotalFill
  FROM fillerCte AS f1
  INNER JOIN fillerCte AS f2 ON f2.rn <= f1.rn
  GROUP BY f1.ID, f1.Filler
), BucketCte as
(
  SELECT
    ID,
    FullCapacity,
    ROW_NUMBER() OVER(ORDER BY ID) AS RN
  FROM @Buckets
),BucketRN
AS
(
  SELECT 
    b1.ID,
    b1.FullCapacity,
    SUM(b2.FullCapacity) - b1.FullCapacity AS RunningTotalCapacity
  FROM BucketCte AS b1 INNER JOIN BucketCte AS b2 ON b2.RN <= b1.RN
  GROUP BY b1.ID, b1.FullCapacity
)
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 FillerRT as f
cross join BucketRN as b
order by f.ID, b.ID;

这将为您提供相同的查询输出。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。