27,579
社区成员
发帖
与我相关
我的任务
分享求一个存储过程的写法！
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是主键。
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是主键。
...全文
请发表友善的回复…
发表回复
jiushaoye 2005-07-04
- 打赏
- 举报
引号不对,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'
就是这样
'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'
pewaloti 2005-07-04
- 打赏
- 举报
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'这样的编译不通过，请问该如何写啊。
+' 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'这样的编译不通过，请问该如何写啊。
pewaloti 2005-07-02
- 打赏
- 举报
差不多就是这个意思了，星期一我测试后给分，谢谢！
jiushaoye 2005-07-02
- 打赏
- 举报
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)
GO
@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)
GO
pewaloti 2005-07-02
- 打赏
- 举报
日期是变量的
aSalt 2005-07-01
- 打赏
- 举报
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
或
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
jiushaoye 2005-07-01
- 打赏
- 举报
没看明白什么意思?
