根据输入的月份生成累计工时

数据库：MSSQL

用存储过程实现：

 

--测试数据：

create table work1
(worktime decimal,workmonth char(2),type varchar(20))
go
create table chanpin(type varchar(20),cpname varchar(60))
go
insert into work1 values(11, '1', 'c01')
insert into work1 values( 11, '1', 'c02')
insert into work1 values(11, '2', 'c02')
insert into work1 values(12, '1', 'c02')
insert into work1 values( 12, '2', 'c02')
insert into work1 values(13, '1', 'c02')
insert into work1 values(14, '1', 'c03')
insert into work1 values(15, '1', 'c03')

insert into chanpin values('c01', 'cc0011')
insert into chanpin values('c02', 'cc0022')
insert into chanpin values('c03', 'cc0033')
go

--需要结果：

产品    产品工时  1月   2月
cc0011   11        11   0
cc0022   59        36   23
cc0033   29        29   0

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

 

create proc my_p1 (@m1 char(2)='0',@m2 char(2)='0',@m3 char(2)='0',@m4 char(2)='0',@m5 char(2)='0',@m6 char(2)='0',
@m7 char(2)='0',@m8 char(2)='0',@m9 char(2)='0',@m10 char(2)='0',@m11 char(2)='0',@m12 char(2)='0')
as
declare @sql varchar(4000)
select @sql='select cpname as ''产品'',sum(worktime) as  ''产品工时'','
if @m1<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m1+''' then worktime else 0 end) as '''+@m1+'月'','
if @m2<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m2+''' then worktime else 0 end) as '''+@m2+'月'','
if @m3<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m3+''' then worktime else 0 end) as '''+@m3+'月'','
if @m4<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m4+''' then worktime else 0 end) as '''+@m4+'月'','
if @m5<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m5+''' then worktime else 0 end) as '''+@m5+'月'','
if @m6<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m6+''' then worktime else 0 end) as '''+@m6+'月'','
if @m7<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m7+''' then worktime else 0 end) as '''+@m7+'月'','
if @m8<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m8+''' then worktime else 0 end) as '''+@m8+'月'','
if @m9<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m9+''' then worktime else 0 end) as '''+@m9+'月'','
if @m10<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m10+''' then worktime else 0 end) as '''+@m10+'月'','
if @m11<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m11+''' then worktime else 0 end) as '''+@m11+'月'','
if @m12<>'0'
 select @sql=@sql+'sum(case workmonth when '''+@m12+''' then worktime else 0 end) as '''+@m12+'月'','
select @sql=left(@sql,len(@sql)-1)
select @sql=@sql+' from work1 w,chanpin c where w.type=c.type group by cpname'
exec(@sql)
go

--调用示例：
my_p1 '1','2'