求一个存储过程的写法！
Year month version o1 n1 o2 n2 o3 n3 o4 n4 ……o31 n31 key
2005 5 6 3 3 4 3 3 3 3 3 3 3 1
2005 5 6 3 3 5 3 8 3 6 8 3 6 2
2005 5 6 3 8 7 7 6 3 3 3 6 8 3
2005 5 6 3 4 3 3 7 8 3 3 8 3 4
2005 5 6 2 6 7 3 3 3 3 7 7 3 5
2005 5 6 1 3 3 6 3 6 3 8 3 6 6
2005 5 6 3 8 3 3 3 3 3 3 7 3 7
表中有年、月、版本，o1代表1号白班，n1代表1号晚班，直到31号。想建立一个存储过程根据提供的当月日期，比如从7号到21号，如何将7号到21号的数据累加起来。
Year，month，key是主键。
问题点数：40、回复次数：7Top
1 楼jiushaoye（打倒小日本）回复于 2005-07-01 16:54:12 得分 0
没看明白什么意思?Top
2 楼yuanarea（Sail before）回复于 2005-07-01 20:16:20 得分 0
select year,month,version,(o7+n7+o8+n8o9+n9...o21+n21) as nx from table where year=your_input_year,month=your_month
或
select year,month,version,SUM(o7+n7+o8+n8o9+n9...o21+n21) as nx from table where year=your_input_year,month=your_month GROUP BY year,month
Top
3 楼pewaloti（）回复于 2005-07-02 15:08:44 得分 0
日期是变量的Top
4 楼jiushaoye（打倒小日本）回复于 2005-07-02 15:15:22 得分 40
CREATE PROCEDURE dbo.test
@n1 int,
@n2 int
AS
declare @sql nvarchar(1000),
@sql2 nvarchar(1000)
set @sql2 = ''
set @sql = ''
while @n1 <= @n2
begin
Set @sql2 = @sql2 + 'o' + convert(nvarchar(2),@n1) + '+' + 'n' + convert(nvarchar(2),@n1) + '+'
set @n1 = @n1 + 1
end
set @sql2 = substring(@sql2,1,len(@sql2) - 1)
--select @sql2,@n1
set @sql = 'select idkey,y,m,v,' + @sql2
+' from t '
--select @sql
execute(@sql)
GOTop
5 楼pewaloti（）回复于 2005-07-02 15:19:23 得分 0
差不多就是这个意思了，星期一我测试后给分，谢谢！Top
6 楼pewaloti（）回复于 2005-07-04 09:39:51 得分 0
set @sql = 'select idkey,y,m,v,' + @sql2
+' from t '
还有小问题，就是我后面的'from t'不是这么简单而是'from (select monthline.*,autoset.si,autoset.asno
from monthline,autoset
where cur_year=@cur_year and cur_month=@cur_month and wno='04' and version =@version and autoset.atno=monthline.atno) a
left join
(select j_no,sequence
from monthline_jscltzd
where cur_year=@cur_year and cur_month=@cur_month and wno='04' and version =@version) b
on a.sequence = b.sequence'这样的编译不通过，请问该如何写啊。
Top
7 楼jiushaoye（打倒小日本）回复于 2005-07-04 10:46:51 得分 0
引号不对,04前面和后面的引号要用两个
就是这样
'from (select monthline.*,autoset.si,autoset.asno
from monthline,autoset
where cur_year=@cur_year and cur_month=@cur_month and wno=''04'' and version =@version and autoset.atno=monthline.atno) a
left join
(select j_no,sequence
from monthline_jscltzd
where cur_year=@cur_year and cur_month=@cur_month and wno=''04'' and version =@version) b
on a.sequence = b.sequence'Top
