SQL Server 2012:按顺序求和给出错误“顺序"附近的语法不正确 [英] SQL Server 2012: sum over order by gives error Incorrect syntax near 'order'
问题描述
declare @t table (cid int, amount int, cname varchar)
insert into @t
values (6, 20, 'C'), (7, 30, 'C'), (8, 10, 'C'), (9, 10, 'D')
select
sum(amount) over (partition by cname order by cid),
*
from @t
抛出错误:
'order' 附近的语法不正确.
Incorrect syntax near 'order'.
https://msdn.microsoft.com/en-us/library/ms187810.aspx
SQL Server 2012 不支持 sum over order by 吗?如果我删除 order by 并仅使用 partition by 它可以工作,但对于 'C' 我得到所有行的 60.我想获得总计.
Isn't sum over order by supported in SQL Server 2012? If I remove order by and use only partition by it works but for 'C' I get 60 for all rows. I want to get running total.
更多信息:
- Microsoft SQL Server 管理工作室 11.0.2100.60
- Microsoft 分析服务客户端工具 11.0.2100.60
- Microsoft 数据访问组件 (MDAC) 6.1.7601.17514
数据库选项 -> 兼容级别仅显示 2000、2005 和 2008,并选择了 2008.
Database option -> compatibility level only shows 2000, 2005 and 2008, with 2008 selected.
尝试运行
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 110
抛出错误:
数据库兼容级别的有效值为 80、90 或 100.
Valid values of the database compatibility level are 80, 90, or 100.
推荐答案
直到 SQL Server 2012+ 才支持累积总和.据推测,您使用的是 SQL Server 2005 或 2008,或者您的兼容性设置设置为 105 或更少(请参阅 此处).
Cumulative sums are not supported until SQL Server 2012+. Presumably, you are using SQL Server 2005 or 2008 or your compatibility setting is set to 105 or less (see here).
在这些版本中,你可以使用outer apply
:
In these versions, you can use outer apply
:
select t.*, s.amount
from @t t outer apply
(select sum(t2.amount) as amount
from @t t2
where t2.cname = t.cname and t2.cid <= t.cid
) s;
这篇关于SQL Server 2012:按顺序求和给出错误“顺序"附近的语法不正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!