我的存储过程,CPChuDeailID即为BSCREATE PROCEDURE [dbo].[Search_TongJiNewName] @name varchar(50), @dt1 DateTime, --开始时间 @dt2 DateTime --结束时间AS BEGIN set nocount on declare @sql nvarchar(4000); set @sql = N'select m.* , n.OutNumber '+char(10)+'from (select XuHao,CPName, DanWei'+CHAR(10); select @sql = @sql+',sum(case WLName when '''+WLName+''' then Number else 0 end) [' + WLName + ']'+CHAR(10) from (select distinct WLName,wlxh from VIEW_CPWL_DuiZhao where CTime between @dt1 and @dt2 and HTCode=@name) as a order by wlxh;set @sql=@sql+' from VIEW_CPWL_DuiZhao t '+char(10)+ 'where CTime between '''+convert(varchar(10),@dt1,120)+''' and '''+convert(varchar(10),@dt2,120)+''''+CHAR(10)+ ' and HTCode='''+@name+''' group by XuHao,CPName , DanWei) m,'+char(10)+ '(select CPName,XuHao, sum(OutNumber) OutNumber '+char(10)+ 'from (select distinct CPName, XuHao, CPChuDeailID, OutNumber'+char(10)+ 'from VIEW_CPWL_DuiZhao where CTime between '''+convert(varchar(10),@dt1,120)+''' and '''+ convert (varchar(10),@dt2,120) + ''' and HTCode='''+@name+''') t'+char(10)+ 'group by CPName, XuHao) n'+char(10)+ 'where m.CPName = n.CPName'+char(10)+ 'order by n.XuHao';exec(@sql);set nocount off END GO
@name varchar(50),
@dt1 DateTime, --开始时间
@dt2 DateTime --结束时间AS
BEGIN
set nocount on declare @sql nvarchar(4000);
set @sql = N'select m.* , n.OutNumber '+char(10)+'from (select XuHao,CPName, DanWei'+CHAR(10);
select @sql = @sql+',sum(case WLName when '''+WLName+''' then Number else 0 end) [' + WLName + ']'+CHAR(10)
from (select distinct WLName,wlxh from VIEW_CPWL_DuiZhao where CTime between @dt1 and @dt2 and HTCode=@name) as a
order by wlxh;set @sql=@sql+' from VIEW_CPWL_DuiZhao t '+char(10)+
'where CTime between '''+convert(varchar(10),@dt1,120)+''' and '''+convert(varchar(10),@dt2,120)+''''+CHAR(10)+
' and HTCode='''+@name+''' group by XuHao,CPName , DanWei) m,'+char(10)+
'(select CPName,XuHao, sum(OutNumber) OutNumber '+char(10)+
'from (select distinct CPName, XuHao, CPChuDeailID, OutNumber'+char(10)+
'from VIEW_CPWL_DuiZhao where CTime between '''+convert(varchar(10),@dt1,120)+''' and '''+ convert
(varchar(10),@dt2,120) + ''' and HTCode='''+@name+''') t'+char(10)+
'group by CPName, XuHao) n'+char(10)+
'where m.CPName = n.CPName'+char(10)+
'order by n.XuHao';exec(@sql);set nocount off
END
GO
create table tb(CPName varchar(20),BS int,WLName varchar(20),OutNumber int, Number int, CTime datetime, DanWei varchar(10), CPXuHao int, WLXuHao int)
insert into tb values('按摩器' ,1 , 'PE胶袋' , 100 , 4 , '2010-09-16' , '千克' , 1 , 23)
insert into tb values('按摩器' ,1 , '非瓦楞彩盒' , 100 , 40 , '2010-09-16' , '千克' , 1 , 19)
insert into tb values('按摩器' ,2 , 'PE胶袋' , 50 , 2 , '2010-09-17' , '千克' , 1 , 23)
insert into tb values('按摩器' ,2 , '非瓦楞彩盒' , 50 , 20 , '2010-09-17' , '千克' , 1 , 19)
insert into tb values('电子音乐卡D' ,5 , '单喇叭仔B' , 200 , 4210 , '2010-09-16' , '千克' , 2 , 14)
insert into tb values('电子音乐卡D' ,5 , '塑胶自贴标签' , 200 , 2 , '2010-09-16' , '千克' , 2 , 15)
insert into tb values('电子音乐卡D' ,5 , '單層空白電路板', 200 , 6 , '2010-09-16' , '千克' , 2 , 4)
insert into tb values('电子音乐卡D' ,7 , '单喇叭仔B' , 100 , 2105 , '2010-09-17' , '千克' , 2 , 14)
insert into tb values('电子音乐卡D' ,7 , '塑胶自贴标签' , 100 , 1 , '2010-09-17' , '千克' , 2 , 15)
insert into tb values('电子音乐卡D' ,7 , '單層空白電路板', 100 , 3 , '2010-09-17' , '千克' , 2 , 4 )
go
create proc my_proc @dt1 as datetime,@dt2 as datetime
as
begin
declare @sql varchar(8000)
set @sql = 'select m.* , n.OutNumber from (select CPName , DanWei'
select @sql = @sql + ' , sum(case WLName when ''' + WLName + ''' then Number else 0 end) [' + WLName + ']'
from (select distinct WLName from tb where CTime between @dt1 and @dt2) as a
set @sql = @sql + ' from tb t where CTime between ''' + convert(varchar(10),@dt1,120) + ''' and ''' + convert(varchar(10),@dt2,120) + ''' group by CPName , DanWei) m , (select CPName , sum(OutNumber) OutNumber from (select distinct CPName , BS , OutNumber from tb where CTime between ''' + convert(varchar(10),@dt1,120) + ''' and ''' + convert(varchar(10),@dt2,120) + ''') t group by CPName) n where m.CPName = n.CPName'
exec(@sql)
end
goexec my_proc '2010-09-15','2010-09-18'drop table tb
drop proc my_proc/*
CPName DanWei PE胶袋 单喇叭仔B 單層空白電路板 非瓦楞彩盒 塑胶自贴标签 OutNumber
-------------------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
按摩器 千克 6 0 0 60 0 150
电子音乐卡D 千克 0 6315 9 0 3 300(所影响的行数为 2 行)
*/
insert into VIEW_CPWL_DuiZhao values('按摩器' , 1 , 'PE膠袋' , 100 , 4 , '2010-09-16' , '千克' , 1 , 23 , '201009')
insert into VIEW_CPWL_DuiZhao values('按摩器' , 1 , '非瓦楞彩盒', 100 , 40 , '2010-09-16' , '千克' , 1 , 19 , '201009')
insert into VIEW_CPWL_DuiZhao values('按摩器' , 2 , 'PE膠袋' , 50 , 2 , '2010-09-17' , '千克' , 1 , 23 , '201009')
insert into VIEW_CPWL_DuiZhao values('按摩器' , 2 , '非瓦楞彩盒', 50 , 20 , '2010-09-17' , '千克' , 1 , 19 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 5 , '空白電路板', 200 , 80 , '2010-09-16' , '千克' , 2 , 14 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 5 , '單喇叭仔' , 200 , 2 , '2010-09-16' , '千克' , 2 , 5 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 5 , '錫線' , 200 , 6 , '2010-09-16' , '千克' , 2 , 4 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 5 , 'PE膠袋' , 200 , 50 , '2010-09-16' , '千克' , 2 , 23 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 7 , '空白電路板', 400 , 160, '2010-09-18' , '千克' , 2 , 14 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 7 , '單喇叭仔' , 400 , 4 , '2010-09-18' , '千克' , 2 , 5 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 7 , '錫線' , 400 , 12 , '2010-09-18' , '千克' , 2 , 4 , '201009')
insert into VIEW_CPWL_DuiZhao values('電子賀卡L', 7 , 'PE膠袋' , 400 , 100, '2010-09-18' , '千克' , 2 , 23 , '201009')
goCREATE PROCEDURE Search_TongJiNewName
@name varchar(50),
@dt1 DateTime, --开始时间
@dt2 DateTime --结束时间
AS
BEGIN
set nocount on
declare @sql nvarchar(4000);
set @sql = N'select m.* , n.OutNumber '+char(10)+'from (select XuHao,CPName, DanWei'+CHAR(10);
select @sql = @sql+',sum(case WLName when '''+WLName+''' then Number else 0 end) [' + WLName + ']'+CHAR(10)
from (select distinct WLName,wlxh from VIEW_CPWL_DuiZhao where CTime between @dt1 and @dt2 and HTCode=@name) as a
order by wlxh;set @sql=@sql+' from VIEW_CPWL_DuiZhao t '+char(10)+
'where CTime between '''+convert(varchar(10),@dt1,120)+''' and '''+convert(varchar(10),@dt2,120)+''''+CHAR(10)+
' and HTCode='''+@name+''' group by XuHao,CPName , DanWei) m,'+char(10)+
'(select CPName,XuHao, sum(OutNumber) OutNumber '+char(10)+
'from (select distinct CPName, XuHao,OutNumber'+char(10)+
'from VIEW_CPWL_DuiZhao where CTime between '''+convert(varchar(10),@dt1,120)+''' and '''+ convert
(varchar(10),@dt2,120) + ''' and HTCode='''+@name+''') t'+char(10)+
'group by CPName, XuHao) n'+char(10)+
'where m.CPName = n.CPName'+char(10)+
'order by n.XuHao';
exec(@sql);
set nocount off
END
GOexec Search_TongJiNewName '201009' , '2010-09-15','2010-09-18'drop table VIEW_CPWL_DuiZhao
drop proc Search_TongJiNewName/*
XuHao CPName DanWei 錫線 單喇叭仔 空白電路板 非瓦楞彩盒 PE膠袋 OutNumber
----------- -------------------- -------------------- ----------- ----------- ----------- ----------- ----------- -----------
1 按摩器 千克 0 0 0 60 6 150
2 電子賀卡L 千克 18 6 240 0 150 600*/