SQL Sum() Over (Order by [])出错

Question

本帖最后由 delao1980 于 2010-01-07 13:17:09 编辑

Answer 1

if object_id('tempdb..#A') is not null drop table #A 
select 'a' as Name,2007 as Year,100 as M 
into #A 
insert into #A select 'a'as Name,2008 as Year,400 as M 
insert into #A select 'a'as Name,2009 as Year,600 as M 
insert into #A select 'a'as Name,2010 as Year,800 as M 
insert into #A select 'b'as Name,2007 as Year,150 as M 
insert into #A select 'b'as Name,2008 as Year,250 as M 
insert into #A select 'b'as Name,2009 as Year,450 as M 
insert into #A select 'b'as Name,2010 as Year,650 as M 
insert into #A select 'c'as Name,2007 as Year,700 as M 
insert into #A select 'c'as Name,2008 as Year,800 as M 
insert into #A select 'c'as Name,2009 as Year,850 as M  
insert into #A select 'c'as Name,2010 as Year,950 as M 
select * from #A 
SELECT NAME,YEAR,(SELECT SUM(M)AS M FROM #A WHERE NAME=T.NAME)AS NUM FROM #A T ORDER BY NAME ,YEAR
SELECT NAME,YEAR, SUM(M)AS M FROM #A T GROUP BY NAME,YEARNAME YEAR        NUM         
---- ----------- ----------- 
a    2007        1900
a    2008        1900
a    2009        1900
a    2010        1900
b    2007        1500
b    2008        1500
b    2009        1500
b    2010        1500
c    2007        4250
c    2008        4250
c    2009        4250
c    2010        4250
c    2010        4250（所影响的行数为 13 行）NAME YEAR        M           
---- ----------- ----------- 
a    2007        100
b    2007        150
c    2007        700
a    2008        400
b    2008        250
c    2008        800
a    2009        600
b    2009        450
c    2009        850
a    2010        800
b    2010        650
c    2010        1900（所影响的行数为 12 行）

Answer 2

我想要的是得到下面这样的结果：
Name Year M SubTotal
a 2007 100 100
a 2008 400 500
a 2009 600 1100
a 2010 800 1900
b 2007 150 150
b 2008 250 400
b 2009 450 850
b 2010 650 1500
c 2007 700 700
c 2008 800 1500
c 2009 850 2350
c 2010 950 3300谢谢！

Answer 3

我使用下面的这个SUM OVER (PARTITION BY [])可以运行得到下面的结果，但不是我想要的：
Name Year M SubTotal
a 2007 100 1900
a 2008 400 1900
a 2009 600 1900
a 2010 800 1900
b 2007 150 1500
b 2008 250 1500
b 2009 450 1500
b 2010 650 1500
c 2007 700 3300
c 2008 800 3300
c 2009 850 3300
c 2010 950 3300

Answer 4

select Name,Year,M,
sum(M) over (partition by name)as SubTotal
from #A
order by Name,Year

Answer 5

使用order by分组来做，希望可以

Answer 6

使用order by 就报错：
select Name,Year,M,
sum(M) over (order by name)as SubTotal
from #A
order by Name,YearMsg 102, Level 15, State 1, Line 18
Incorrect syntax near 'order'.

Answer 7

if object_id('tempdb..#A') is not null drop table #A 
select 'a' as Name,2007 as Year,100 as M 
into #A 
insert into #A select 'a'as Name,2008 as Year,400 as M 
insert into #A select 'a'as Name,2009 as Year,600 as M 
insert into #A select 'a'as Name,2010 as Year,800 as M 
insert into #A select 'b'as Name,2007 as Year,150 as M 
insert into #A select 'b'as Name,2008 as Year,250 as M 
insert into #A select 'b'as Name,2009 as Year,450 as M 
insert into #A select 'b'as Name,2010 as Year,650 as M 
insert into #A select 'c'as Name,2007 as Year,700 as M 
insert into #A select 'c'as Name,2008 as Year,800 as M 
insert into #A select 'c'as Name,2009 as Year,850 as M  
insert into #A select 'c'as Name,2010 as Year,950 as M 
select * from #A 
SELECT NAME,YEAR,M,(
SELECT SUM(M)AS M FROM #A WHERE NAME=T.NAME AND Year<=T.Year)AS NUM 
FROM #A T 
ORDER BY NAME ,YEARNAME YEAR        M           NUM         
---- ----------- ----------- ----------- 
a    2007        100         100
a    2008        400         500
a    2009        600         1100
a    2010        800         1900
b    2007        150         150
b    2008        250         400
b    2009        450         850
b    2010        650         1500
c    2007        700         700
c    2008        800         1500
c    2009        850         2350
c    2010        950         4250
c    2010        950         4250（所影响的行数为 13 行）

Answer 8

if object_id('tempdb..#A') is not null drop table #A 
select 'a' as Name,2007 as Year,100 as M 
into #A 
insert into #A select 'a'as Name,2008 as Year,400 as M 
insert into #A select 'a'as Name,2009 as Year,600 as M 
insert into #A select 'a'as Name,2010 as Year,800 as M 
insert into #A select 'b'as Name,2007 as Year,150 as M 
insert into #A select 'b'as Name,2008 as Year,250 as M 
insert into #A select 'b'as Name,2009 as Year,450 as M 
insert into #A select 'b'as Name,2010 as Year,650 as M 
insert into #A select 'c'as Name,2007 as Year,700 as M 
insert into #A select 'c'as Name,2008 as Year,800 as M 
insert into #A select 'c'as Name,2009 as Year,850 as M  
insert into #A select 'c'as Name,2010 as Year,950 as M 
select * from #A 
SELECT NAME,YEAR,M,(
SELECT SUM(M)AS M FROM #A WHERE NAME=T.NAME AND Year<=T.Year)AS NUM 
FROM #A T 
ORDER BY NAME ,YEARNAME YEAR        M           NUM         
---- ----------- ----------- ----------- 
a    2007        100         100
a    2008        400         500
a    2009        600         1100
a    2010        800         1900
b    2007        150         150
b    2008        250         400
b    2009        450         850
b    2010        650         1500
c    2007        700         700
c    2008        800         1500
c    2009        850         2350
c    2010        950         3300（所影响的行数为 12 行）晕,上面的复制出错

Answer 9

谢谢！是我想要的结果。
请问为什么这样的语句都会报错呢：
sum([]) over (partition by [] order by [])
sum([]) over (order by [])
我在baidu上搜索到很多这样的语句应用，为什么我这里用就不行呢？
我用2005和2008都试过，都不行。
请高手指点。

Answer 10

count、sum、min、max 这样的聚合函数不能用于 over (order by ) 子句，只能用于 over (partition by) 子句。可以参看联机丛书。

Answer 11

select Name,Year,M, 
sum(M) over (partition by name)as SubTotal 
from #A Name Year        M           SubTotal
---- ----------- ----------- -----------
a    2008        400         1900
a    2009        600         1900
a    2010        800         1900
a    2007        100         1900
b    2007        150         1500
b    2008        250         1500
b    2009        450         1500
b    2010        650         1500
c    2007        700         3300
c    2008        800         3300
c    2009        850         3300
c    2010        950         3300(12 行受影响) 

Answer 12

如果 SELECT 子句 <select list> 中包含聚合函数，则 GROUP BY 将计算每组的汇总值。指定 GROUP BY 时，选择列表中任意非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。

Answer 13

今天也遇到同样的问题，但是最近我在看一本<<SQL Cook Book>>。这本书比较权威。里面的相关章节就将到了这个问题是可以的，奇怪了！

Answer 14

今天也遇到同样的问题，但是最近我在看一本<<SQL Cook Book>>。这本书比较权威。里面的相关章节就将到了这个问题是可以的，奇怪了！

Answer 15

今天也遇到同样的问题，但是最近我在看一本<<SQL Cook Book>>。这本书比较权威。里面的相关章节就将到了这个问题是可以的，奇怪了！