基础信息表内容:
CREATE TABLE [dbo].[基础数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[起始时间] [datetime],
[累加月份] [int] NULL,
[地区编码] [varchar] (8)
) ON [PRIMARY] 结果数据表内容:
CREATE TABLE [dbo].[结果数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[月初日期] [datetime],
[月末日期] [datetime],
[所属年] [varchar] (4),
[所属月] [varchar] (4),
[地区编码] [varchar] (8)
) ON [PRIMARY]
基础信息表内容
Guid 名称 金额 开始时间 累加月份 地区编码
1 名字1 100.00 2009-2-07 3 001
2 名字4 300.00 2009-7-13 2 003 结果数据表内容
Guid 名称 金额 月初日期 月末日期 所属年 所属月 地区编码
1 名字1 100.00 2009-2-01 2009-2-28 2009 02 001
2 名字1 100.00 2009-3-01 2009-3-31 2009 03 001
3 名字1 100.00 2009-4-01 2009-4-30 2009 04 001
4 名字4 300.00 2009-7-01 2009-7-31 2009 07 003
5 名字4 300.00 2009-8-01 2009-8-31 2009 08 003 用存储过程实现从基础信息表生成结果数据表信息
注意1:存储过程需要传地区编码参数
注意2:基础信息表的起始时间为任意一天,累加月份表示需要生成几个月的数据(取起始时间月份累加)。
注意3:结果数据表月初日期取对应月第一天,月末日期取对应月最后一天。年月为累加结果。
请大家根据这个例子写一个存储过程,最好有备注,我好好学习一下!谢谢各位老师!!!!!备注:必须用以下模式实现基础数据表按照地区编码取数
循环"记录数"
{
取记录的“累加月份数”
嵌套循环“累加月份数”
{
插入结果数据表创建记录
}
} 该问题已经开贴,对应贴办法不需要再写了!
http://topic.csdn.net/u/20090911/11/ae6de2bf-6ade-4efd-a091-32ddd269635e.html?73645主要是学习循环的模式~~因为以后类似的应用比较多!!
CREATE TABLE [dbo].[基础数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[起始时间] [datetime],
[累加月份] [int] NULL,
[地区编码] [varchar] (8)
) ON [PRIMARY] 结果数据表内容:
CREATE TABLE [dbo].[结果数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[月初日期] [datetime],
[月末日期] [datetime],
[所属年] [varchar] (4),
[所属月] [varchar] (4),
[地区编码] [varchar] (8)
) ON [PRIMARY]
基础信息表内容
Guid 名称 金额 开始时间 累加月份 地区编码
1 名字1 100.00 2009-2-07 3 001
2 名字4 300.00 2009-7-13 2 003 结果数据表内容
Guid 名称 金额 月初日期 月末日期 所属年 所属月 地区编码
1 名字1 100.00 2009-2-01 2009-2-28 2009 02 001
2 名字1 100.00 2009-3-01 2009-3-31 2009 03 001
3 名字1 100.00 2009-4-01 2009-4-30 2009 04 001
4 名字4 300.00 2009-7-01 2009-7-31 2009 07 003
5 名字4 300.00 2009-8-01 2009-8-31 2009 08 003 用存储过程实现从基础信息表生成结果数据表信息
注意1:存储过程需要传地区编码参数
注意2:基础信息表的起始时间为任意一天,累加月份表示需要生成几个月的数据(取起始时间月份累加)。
注意3:结果数据表月初日期取对应月第一天,月末日期取对应月最后一天。年月为累加结果。
请大家根据这个例子写一个存储过程,最好有备注,我好好学习一下!谢谢各位老师!!!!!备注:必须用以下模式实现基础数据表按照地区编码取数
循环"记录数"
{
取记录的“累加月份数”
嵌套循环“累加月份数”
{
插入结果数据表创建记录
}
} 该问题已经开贴,对应贴办法不需要再写了!
http://topic.csdn.net/u/20090911/11/ae6de2bf-6ade-4efd-a091-32ddd269635e.html?73645主要是学习循环的模式~~因为以后类似的应用比较多!!
create procedure Proc
@resCode varchar(20)
as
set nocount on
declare CurT cursor for
select Guid,名称,金额,开始时间,累加月份 from 基础数据表 where 地区编码=@resCode open curT declare @guid varchar(36),@Name varchar(20),@je numeric(18,2),@startDate datetime,@lj int fetch next from CurT into @guid ,@Name ,@je ,@startDate ,@lj truncate table 结果数据表 while @@fetch_status=0 begin
declare @i int
select @i=0
while @i<@lj begin
insert into 结果数据表(Guid,名称,金额,月初日期,月末日期,所属年,所属月,地区编码 )
values(@guid,@Name,@je,
dateadd(M,i,dateadd(d,1-day(起始时间),起始时间) ),
dateadd(D,-DAY(起始时间),dateadd(M,@i+1,起始时间 )),
year(dateadd(M,@i,起始时间 ))[所属年],
month(dateadd(M,@i,起始时间 )),@resCode)
select @i=@i+1
end
fetch next from CurT into @guid ,@Name ,@je ,@startDate ,@lj
end close curT
deallocate curT set nocount off select * from 结果数据表
---------- 添加测试表-------------
if object_id('基础数据表')is not null drop table 基础数据表
go
CREATE TABLE [dbo].[基础数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[起始时间] [datetime],
[累加月份] [int] NULL,
[地区编码] [varchar] (8)
)
insert 基础数据表 select
'1','名字1', 100.00 , '2009-2-07', 3 , '001' union all select
'2','名字4' , 300.00, '2009-7-13' , 2, '003'
if object_id('结果数据表')is not null drop table 结果数据表
go
CREATE TABLE [dbo].[结果数据表] (
[Guid] [varchar] (36) ,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[月初日期] [datetime],
[月末日期] [datetime],
[所属年] [varchar] (4),
[所属月] [varchar] (4),
[地区编码] [varchar] (8)
) -------------------创建存储过程----------------------
if object_id('pro')is not null drop proc pro
gocreate proc pro
@start_bm varchar(8),
@end_bm varchar(8)
as
select *,id=identity(int,1,1) into #t from [基础数据表] where cast([地区编码] as int) between @start_bm and @end_bm
declare @n int,@m int,@ljys int,@j int
set @n=1
set @m=(select count(*) from #t)
while @n<=@m ---循环记录
begin
set @ljys=(select [累加月份] from #t where id=@n)
set @j=1
while @j<=@ljys
begin
insert [结果数据表]
select Guid,名称,金额,
DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,dateadd(month,@j-1,[起始时间])))+1,0),
dateadd(day,-1,DATEADD(mm, DATEDIFF(mm,0,dateadd(month,@j-1,[起始时间]))+1, 0)),
right('0'+ltrim(year(dateadd(month,@j-1,[起始时间]))),2),
right('0'+ltrim(month(dateadd(month,@j-1,[起始时间]))),2),
[地区编码]
from
#t
where
id=@n
set @j=@j+1
end
set @n=@n+1
end
go
-------------------执行存储过程---------
exec pro '001','002'
select * from [结果数据表]
----------------------结果---------------
Guid 名称 金额 月初日期 月末日期 所属年 所属月 地区编码
------------------------------------ -------------------- --------------------------------------- ----------------------- ----------------------- ---- ---- --------
1 名字1 100.00 2009-02-01 00:00:00.000 2009-02-28 00:00:00.000 09 02 001
1 名字1 100.00 2009-03-01 00:00:00.000 2009-03-31 00:00:00.000 09 03 001
1 名字1 100.00 2009-04-01 00:00:00.000 2009-04-30 00:00:00.000 09 04 001(3 行受影响)
---------- 添加测试表-------------
if object_id('基础数据表')is not null drop table 基础数据表
go
CREATE TABLE [dbo].[基础数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[起始时间] [datetime],
[累加月份] [int] NULL,
[地区编码] [varchar] (8)
)
insert 基础数据表 select
'1','名字1', 100.00 , '2009-2-07', 3 , '001' union all select
'2','名字4' , 300.00, '2009-7-13' , 2, '003'
if object_id('结果数据表')is not null drop table 结果数据表
go
CREATE TABLE [dbo].[结果数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[月初日期] [datetime],
[月末日期] [datetime],
[所属年] [varchar] (4),
[所属月] [varchar] (4),
[地区编码] [varchar] (8)
) -------------------创建存储过程----------------------
if object_id('pro')is not null drop proc pro
gocreate proc pro
@bm varchar(8)
as
select Guid,名称,金额,
[月初日期]=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,dateadd(month,number,[起始时间])))+1,0),
[月末日期]=dateadd(day,-1,DATEADD(mm, DATEDIFF(mm,0,dateadd(month,number,[起始时间]))+1, 0)),
[所属年]=right('0'+ltrim(year(dateadd(month,number,[起始时间]))),2),
[所属月]=right('0'+ltrim(month(dateadd(month,number,[起始时间]))),2),
[地区编码]
from
基础数据表 a,
master..spt_values b
where
number>=0
and number<[累加月份] and type='p'
and 地区编码=@bm
go
-------------------执行存储过程---------
exec pro '001'
----------------------结果---------------Guid 名称 金额 月初日期 月末日期 所属年 所属月 地区编码
------------------------------------ -------------------- --------------------------------------- ----------------------- ----------------------- ---- ---- --------
1 名字1 100.00 2009-02-01 00:00:00.000 2009-02-28 00:00:00.000 09 02 001
1 名字1 100.00 2009-03-01 00:00:00.000 2009-03-31 00:00:00.000 09 03 001
1 名字1 100.00 2009-04-01 00:00:00.000 2009-04-30 00:00:00.000 09 04 001(3 行受影响)
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[起始时间] varchar(10),
[累加月份] [int] NULL,
[地区编码] [varchar] (8)
) ON [PRIMARY] 结果数据表内容:
CREATE TABLE [dbo].[结果数据表] (
[Guid] [varchar] (36) PRIMARY Key,
[名称] [varchar] (20),
[金额] [decimal](19, 2),
[月初日期] [datetime],
[月末日期] [datetime],
[所属年] [varchar] (4),
[所属月] [varchar] (4),
[地区编码] [varchar] (8)
) ON [PRIMARY] insert [基础数据表]
select
1, '名字1', 100.00 , '2009-2-07', 3, 001 union all select
2, '名字4', 300.00, '2009-7-13', 2, 003 insert [结果数据表]
select
1 , '名字1' ,100.00, '2009-2-01', '2009-2-28' , 2009 , '02', '001' union all select
2 , '名字1' , 100.00, '2009-3-01', '2009-3-31' , 2009 , '03', '001' union all select
3 , '名字1' ,100.00, '2009-4-01' , '2009-4-30' , 2009 , '04' , '001' union all select
4 , '名字4' , 300.00, '2009-7-01', '2009-7-31' ,2009 , '07', '003' union all select
5 , '名字4', 300.00, '2009-8-01', '2009-8-31' ,2009, '08', '003' select alter proc proc_name
@bm nvarchar(20),
@start_rq nvarchar(20),
@end_rq nvarchar(20)
as
declare @i1 int,
@i int, --当前记录数
@cur int,
@i2 int ,
@count int
set @i1=0
set @i=1
set @cur=0
set @i2=1
set @count=0
select @i1=@i1+[累加月份] from [基础数据表]
set @count=@i1
while @i1>=1 --总循环次数
begin
select @cur=[累加月份] from [基础数据表] where [Guid]=@i
while @cur>=1
begin
insert [结果数据表]
select @count-@i1+1,[名称],[金额],DATEADD(month,@i2-1,[起始时间]),
dateadd(day,-1,convert(varchar(8),DATEADD(month,@i2,[起始时间]),120)+'01'),
datepart(year,[起始时间]),
datepart(month,[起始时间]),cast([地区编码]+000 as varchar)
from [基础数据表] where [Guid]=@i
set @cur=@cur-1
set @i1=@i1-1
set @i2=@i2+1
end
set @i=@i+1
end
exec proc_name '001','2009-2-07','2009-7-13'select * from 结果数据表
/*
Guid 名称 金额 月初日期 月末日期 所属年 所属月 地区编码
1 名字1 100.00 2009-02-07 00:00:00.000 2009-02-28 00:00:00.000 2009 2 1
2 名字1 100.00 2009-03-07 00:00:00.000 2009-03-31 00:00:00.000 2009 2 1
3 名字1 100.00 2009-04-07 00:00:00.000 2009-04-30 00:00:00.000 2009 2 1
4 名字4 300.00 2009-10-13 00:00:00.000 2009-10-31 00:00:00.000 2009 7 3
5 名字4 300.00 2009-11-13 00:00:00.000 2009-11-30 00:00:00.000 2009 7 3
*/
alter proc proc_name
@bm nvarchar(20),
@start_rq nvarchar(20),
@end_rq nvarchar(20)
as
declare @i1 int,
@i int, --当前记录数
@cur int,
@i2 int ,
@count int
set @i1=0
set @i=1
set @cur=0
set @i2=1
set @count=0
select @i1=@i1+[累加月份] from [基础数据表] where [地区编码]='001'
set @count=@i1
while @i1>=1 --总循环次数
begin
select @cur=[累加月份] from [基础数据表] where [Guid]=@i
while @cur>=1
begin
insert into 结果数据表
select @count-@i1+1,[名称],[金额],DATEADD(month,@i2-1,[起始时间]),
dateadd(day,-1,convert(varchar(8),DATEADD(month,@i2,[起始时间]),120)+'01'),
datename(year,[起始时间]),
datename(month,[起始时间]),right(cast(1000+[地区编码] as varchar),3)
from [基础数据表] where [Guid]=@i
set @cur=@cur-1
set @i1=@i1-1
set @i2=@i2+1
end
set @i=@i+1
end
exec proc_name '001','2009-2-07','2009-7-13'select * from 结果数据表
/*
1 名字1 100.00 2009-02-07 00:00:00.000 2009-02-28 00:00:00.000 2009 02 001
2 名字1 100.00 2009-03-07 00:00:00.000 2009-03-31 00:00:00.000 2009 02 001
3 名字1 100.00 2009-04-07 00:00:00.000 2009-04-30 00:00:00.000 2009 02 001
*/