/* test110 '2023-04-26','2023-04-26','全部','901100569,901100570,901100571,901100572,901100573'; */ ALTER PROCEDURE [dbo].[test110] @StartDate datetime, @EndDate datetime , @uname VARCHAR(50)='全部', @goodslist VARCHAR(MAX) AS set nocount on SET @EndDate=@EndDate+1 create table #t1( goodsno varchar(50) ) create table #dtzd( column_name varchar(1150) , ordinal_position int ) declare @sql varchar(MAX) set @sql='insert into #t1(goodsno) select col='''+ replace(@goodslist,',',''' union all select ''')+'''' PRINT @sql exec (@sql) /*保存基础数据*/ select a.num,a.md_uid,e.uname,b.sale_man,f.work_no,f.pname,b.gid, ( b.SUM_SALE ) jine, ( b.amount ) amount--,count(DISTINCT a.num) as rc into #raw1 from td_outhead a with(nolock) join td_outvoice b with(nolock) on(a.num=b.num) join tr_matecode c with(nolock) on(b.gid=c.gid) --join TD_PAY_INFO d with(nolock) on a.num=d.num join tr_unit e with(nolock) on a.md_uid=e.uid join #t1 X on X.goodsno=c.goodsno LEFT JOIN tr_person f with(nolock) on b.sale_man= f.pid where a.cw_date>=@StartDate and a.cw_date <@EndDate and( uname=@uname or @uname='全部') -- group by a.md_uid,uname,b.sale_man,f.work_no,f.pname ORDER BY a.md_uid,f.work_no ; declare @sql1 varchar(max) set @sql1='SELECT a.md_uid,[uname] as 门店名称,[work_no] as 工号,[pname] 销售员 ,convert( DECIMAL(12,2),sum(a.jine)) as 总金额, convert( DECIMAL(12,2),sum(a.amount)) as 总数量,count(DISTINCT a.num) as 单据数' select @sql1=@sql1+' , convert( DECIMAL(12,2),sum(case gid when '''+ CONVERT(VARCHAR(10),b.gid) +''' then a.jine else 0 end) ) as ['+ CONVERT(VARCHAR(10),b.goodsno)+'/<br/>'+ b.comname +'/<br/>'+b.spec+'(金额)]' +' , convert( DECIMAL(12,2),sum(case gid when '''+ CONVERT(VARCHAR(10),b.gid) +''' then a.amount else 0 end)) as ['+ CONVERT(VARCHAR(10),b.goodsno)+'/<br/>'+ b.comname +'/<br/>'+b.spec+'(数量)]' from #t1 a join TR_MATECODE b on a.goodsno=b.goodsno ORDER BY a.goodsno set @sql1=@sql1+' into #res from #raw1 a GROUP BY a.md_uid,[uname],[work_no],[pname] order by a.md_uid,[work_no] ; insert into #dtzd(column_name,ordinal_position) SELECT column_name,ordinal_position --获取自动生成的列名字 FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = object_name( object_id(''tempdb..#res''), (SELECT database_id FROM sys.databases WHERE name = ''tempdb'')) and ordinal_position>4 -- 第5列开始计算合计值 order by ordinal_position; ' exec(@sql1) ; /*执行一次给 #dtzd 赋值*/ set @sql1=@sql1+' SELECT * from #res union all select null, ''合计:'', null, null' -- 第5列开始计算合计值 select @sql1=@sql1+' ,sum(['+column_name+']) ' from #dtzd order by ordinal_position; set @sql1=@sql1+' from #res ' PRINT(@sql1); exec(@sql1) ; --SELECT * from #dtzd /*--OLD SELECT md_uid,uname,sale_man,work_no,pname, CONVERT(DECIMAL(12,2),jine) as jine, CONVERT(float,amount) as amount,rc from #res union ALL SELECT NULL,'合计:',NULL,NULL,NULL, CONVERT(DECIMAL(12,2), sum(jine) ) as jine, CONVERT(float, sum(amount) ) as amount, sum(rc) from #res */
标签:join,--,sql1,SQLSERVER,生成,amount,position,动态,goodsno From: https://www.cnblogs.com/agfox123/p/17360904.html