将SQL Server 2012查询转换为SQL 2008 [英] Convert SQL server 2012 query to SQL 2008
问题描述
我有以下查询在2012年正常使用,因为它使用2012年的功能。任何人都可以在2008年使用相同的输出完成相同的查询工作吗?
声明 @ Buckets table (ID char ( 2 ),FullCapacity int )
声明 @ Filler table (ID char ( 2 ),填充 int )
插入 进入 @ Buckets 值
(' B1', 100 ),
(' B2',<温泉n class =code-digit> 50 ),
(' B3', 70 )
insert 进入 @ Filler 值
(' F1', 90 ),
(' F2', 70 ),
(' F3', 40 ),
(' F4', 20 )
; fillerCte as
(
选择
ID,
填充,
总和(F iller) over ( order by ID) as TotalFill
来自 @ Filler
),
BucketCte as
(
select
ID,
FullCapacity,
sum(FullCapacity) over ( order 按 ID) - FullCapacity as RunningTotalCapacity
来自 @ Buckets
)
选择
b.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
来自 fillerCte as f
cross join BucketCte as b
order by f.ID,b.ID
我的尝试: < br $>
声明 @ Buckets table (ID char ( 2 ),FullCapacity int )
声明 @ Filler table (ID char (< span class =code-digit> 2 ),填充 int )
将 插入 @ Buckets 值
(' B1', 100 ),
(' B2', 50 ),
(' B3', 70 )
insert into @ Filler 值
(' F1', 90 ),
(' F2' , 70 ),
(' F3' , 40 ),
(' F4', 20 )
; fillerCte as
(
select
ID,
Filler ,
sum(填充程序) over ( order 通过 ID) TotalFill
来自 @Filler
),
BucketCte as
(
选择
ID,
FullCapacity,
sum(FullCapacity) over ( order by ID) - FullCapacity as RunningTotalCapacity
来自 @ Buckets
)
选择
b.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
来自 fillerCte as f
cross join BucketCte as b
order by f.ID,b.ID
我发现了这个:
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 值
(' B1', 100 ),
(' B2', 50 ),
(' B3', 70 )
插入 进入 @ Filler 值
(' F1',< span class =code-digit> 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
< span class =code-keyword> 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
这篇关于将SQL Server 2012查询转换为SQL 2008的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!