--给出一个简单的框架,要求(3)中的参数就留给楼主自己加:create procedure sp_test(@EmplNo int,@SDate datetime,@EDate datetime)
as
begin
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
set @s1='select EmplNo,''Profit'' as 统计项目'
set @s2='union all select EmplNo,''Reward'' as 统计项目'
set @s3='union all select EmplNo,''Scene '' as 统计项目'
set @s4='union all select EmplNo,''其它项'' as 统计项目'
select @s1=@s1+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then Profit else 0 end)',
@s2=@s2+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then Reward else 0 end)',
@s3=@s3+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then Scene else 0 end)',
@s4=@s4+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then ... else 0 end)', --替换...为多个其他字段相加的形式
from
(select
distinct convert(char(8),DateIn,112) as DateIn
from
SaleMain
where
DateIn between @SDate and @EDate) a
order by a.DateIn
set @s1=@s1+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo' set @s2=@s2+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo' set @s3=@s3+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo' set @s4=@s4+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo'
exec(@s1+@s2+@s3+@s4+' order by EmplNo,
case 统计项目
when ''Profit'' then 1
when ''Reward'' then 2
when ''Scene'' then 3
else 4
end')
end
go
as
begin
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
set @s1='select EmplNo,''Profit'' as 统计项目'
set @s2='union all select EmplNo,''Reward'' as 统计项目'
set @s3='union all select EmplNo,''Scene '' as 统计项目'
set @s4='union all select EmplNo,''其它项'' as 统计项目'
select @s1=@s1+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then Profit else 0 end)',
@s2=@s2+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then Reward else 0 end)',
@s3=@s3+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then Scene else 0 end)',
@s4=@s4+',['+right(DateIn,6)+']=sum(case when datediff(dd,DateIn,'''
+DateIn+''')=0 then ... else 0 end)', --替换...为多个其他字段相加的形式
from
(select
distinct convert(char(8),DateIn,112) as DateIn
from
SaleMain
where
DateIn between @SDate and @EDate) a
order by a.DateIn
set @s1=@s1+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo' set @s2=@s2+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo' set @s3=@s3+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo' set @s4=@s4+' from SaleMain a,SaleProfit b where a.FlowNo=b.FlowNo '
+' and b.EmplNo='''+@EmplNo+''''
+' and a.DateIn between '''+convert(char(10),@SDate,120)+''' and '''+convert(char(10),@EDate,120)+''''
+' group by EmplNo'
exec(@s1+@s2+@s3+@s4+' order by EmplNo,
case 统计项目
when ''Profit'' then 1
when ''Reward'' then 2
when ''Scene'' then 3
else 4
end')
end
go
如果被统计的数据或者被用于统计的数据量不大,而整个数据表的数据量很庞大,不妨根据存储过程传入的各项参数,将被用于统计的数据insert到一张本地临时表,然后根据临时表操作。这样做有两个好处:
1、或许有利于查询执行效率的提升
2、有利于存储过程内部代码的精简,不必重复的组织筛选条件拼接到SQL字符串中。
这个报表会被经常用到的,也是想用一个过程来实现,谢谢 libin_ftsafe 的建议,再研究看看...
现在想分解出来做,但又没有好的思路先将表的 横向 转为纵向的,再通过 WITH ROLLUP 来处理,行得通不?请各位大侠进发表一下建议呀