分段+动态列统计的存储过程-完整描述
表MyTable结构如下:
Date1 Value1 ... Value10 ... ValueN
2001-1-1 100 ... 1 ...
2001-1-1 110 ... 3 ...
2001-1-2 90 ... 2 ...
2001-1-3 200 ... 5 ...
2001-1-3 140 ... 3 ...
2001-1-5 150 ... 1 ...
欲得到下面结构的统计结果
Date1 Value1_Sum Value10_Avg ... ValueN_Stat
2001-1-1 210 2 ...
2001-1-2 90 2 ...
2001-1-3 340 4 ...
2001-1-4 0 0 ...
2001-1-5 150 1 ...
要求:
根据传入的参数, 动态生成统计结果集合,返回
过程参数列表:
@prm_StatList --统计列串
@prm_Conditon --条件列串
@prm_StartDate --开始日期
@prm_EndDate --结束日期
@prm_StatType --统计类型[0:全部统计, 1:按日统计, 2:按月统计, 3:按季统计; 4:按年统计]
主要问题是:
1. 统计的列和方式, 如Sum(Value1), Avg(Value2), Sum(ValueX), 并非固定, 是由外部
以参数的形式传如过程内部,如@@prm_StatList,外部调用时赋值为:Sum(Value1), Avg(Value10), Sum(ValueN), ...;
2. 条件也是由外部传如过程内部, 如@prm_Condition, 外部调用时赋值为: Value1>100 AND ValueN =10...;
3. 以@prm_StartDate和@prm_EndDate为区间, 按照@prm_StatType进行统计
4. 不要用Group By...
下面是个实际的example:
@prm_StatList = 'SELECT Sum(Value1), Avg(Value10), Sum(Value11)'
@prm_Conditon = 'Value1>100 AND Value10>1'
@prm_StartDate = '2001-1-1'
@prm_EndDate = '2002-2-2'
@prm_StatType = 2
这样拼接的到:
SELECT @BaseSql = 'SELECT Sum(Value1), Avg(Value10), Sum(Value11) FROM MyTable WHERE Value1>100 AND Value10>1'
之后, 按月累加做循环直到@prm_EndDate:
While (到@prm_EndDate?)
BEGIN
在@BaseSql后加本次时间段作为条件, 如: SELECT @BaseSql = @BaseSql + 'Date1>=2001-1-1 AND Date1<2001-2-1'(第2次则为'Date1>=2001-2-1 AND Date1<2001-3-1')
执行 @BaseSql, 把本次统计结果insert到临时表.
END
最后就得到了统计结果集--临时表, 返回
希望大家多帮助...
问题点数：100、回复次数：11Top
1 楼zjcxc（邹建）回复于 2005-04-19 13:58:20 得分 0
还是这个问题Top
2 楼kingbao（大宝）回复于 2005-04-19 14:01:24 得分 0
是啊,我都抓破头皮了...2#$@#$@Top
3 楼zjcxc（邹建）回复于 2005-04-19 14:12:22 得分 0
茅盾,既然有聚合函数
Sum(Value1), Avg(Value10), Sum(Value11)
那不group by ,这些聚集函数如何计算?
其实有group by 一直就是因为聚合函数的问题,如果你不要group by ,就不要聚集函数,如果你要聚集函数,就要group by ,除非只有聚集函数的列Top
4 楼kingbao（大宝）回复于 2005-04-19 14:20:07 得分 0
比如这样啊 zjcxc(邹建) :
SELECT Sum(Value1), Avg(Value10), Sum(Value11) FROM MyTable WHERE Value1>100 AND Value10>1Top
5 楼kingbao（大宝）回复于 2005-04-19 15:10:55 得分 0
邹建兄,再帮我看看...Top
6 楼zjcxc（邹建）回复于 2005-04-19 15:11:41 得分 0
--测试数据
create table MyTable(Date1 datetime,Value1 int,Value10 int)
insert MyTable select '2001-1-1',100,1
union all select '2001-1-1',110,3
union all select '2001-1-2', 90,2
union all select '2001-1-3',200,5
union all select '2001-1-3',140,3
union all select '2001-1-5',150,1
go
--查询的存储过程
create proc p_qry
@prm_StatList nvarchar(1000), --统计列串
@prm_Conditon nvarchar(1000), --条件列串
@prm_StartDate datetime, --开始日期
@prm_EndDate datetime, --结束日期
@prm_StatType int
as
set nocount on
declare @s1 nvarchar(4000),@s nvarchar(4000),@tbname sysname,@dt datetime
select @tbname=quotename(N'##'+rtrim(newid())),
@s1=case
when isnull(@prm_Conditon,'')='' then 'where (Date1>=@dt1 and Date1<@dt2)'
else 'where ('+@prm_Conditon+') and (Date1>=@dt1 and Date1<@dt2)' end,
@s=N'insert '+@tbname+' select '+@prm_StatList+N' from MyTable '+@s1,
@s1=N'select id=identity(int,1,1),'+@prm_StatList+N' into '+@tbname+' from MyTable '+@s1,
@prm_StartDate=convert(char(10),@prm_StartDate,120),
@prm_EndDate=dateadd(day,1,convert(char(10),@prm_EndDate,120))
if @prm_StatType=0 --不知道全部统计是代表怎么统计?
begin
print '写全部统计的处理代码'
end
else if @prm_StatType=1
begin
set @dt=dateadd(day,1,@prm_StartDate)
exec sp_executesql @s1,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
while @dt<@prm_EndDate
begin
select @prm_StartDate=@dt,
@dt=dateadd(day,1,@prm_StartDate)
exec sp_executesql @s,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
end
end
else if @prm_StatType=2
begin
set @dt=dateadd(month,1,dateadd(day,1-day(@prm_StartDate),@prm_StartDate))
if @dt>@prm_EndDate set @dt=@prm_EndDate
exec sp_executesql @s1,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
while @dt<@prm_EndDate
begin
select @prm_StartDate=@dt,
@dt=case
when dateadd(month,1,@prm_StartDate)>@prm_EndDate
then @prm_EndDate
else dateadd(month,1,@prm_StartDate) end
exec sp_executesql @s,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
end
end
else if @prm_StatType=3
begin
set @dt=CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@prm_StartDate)*3+1,DATEADD(Month,-Month(@prm_StartDate),@prm_StartDate)),120)+'1'
if @dt>@prm_EndDate set @dt=@prm_EndDate
exec sp_executesql @s1,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
while @dt<@prm_EndDate
begin
select @prm_StartDate=@dt,
@dt=case
when dateadd(Quarter,1,@prm_StartDate)>@prm_EndDate
then @prm_EndDate
else dateadd(Quarter,1,@prm_StartDate) end
exec sp_executesql @s,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
end
end
else if @prm_StatType=4
begin
set @dt=dateadd(year,1,convert(char(5),@prm_StartDate,120)+'-1-1')
if @dt>@prm_EndDate set @dt=@prm_EndDate
exec sp_executesql @s1,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
while @dt<@prm_EndDate
begin
select @prm_StartDate=@dt,
@dt=case
when dateadd(year,1,@prm_StartDate)>@prm_EndDate
then @prm_EndDate
else dateadd(year,1,@prm_StartDate) end
exec sp_executesql @s,N'@dt1 datetime,@dt2 datetime',@prm_StartDate,@dt
end
end
exec('select * from '+@tbname+' order by id;drop table '+@tbname)
go
--调用
exec p_qry 'Value1_Sum=isnull(sum(Value1),0),Value10_Avg=isnull(Avg(Value10),0)',
'','2001-1-1','2001-12-5',3
go
--删除测试
drop table MyTable
drop proc p_qry
Top
7 楼zjcxc（邹建）回复于 2005-04-19 15:13:00 得分 0
要用循环,感觉特没效率,分组统计其实是最好的Top
8 楼zjcxc（邹建）回复于 2005-04-19 15:23:44 得分 100
--简化一下
--测试数据
create table MyTable(Date1 datetime,Value1 int,Value10 int)
insert MyTable select '2001-1-1',100,1
union all select '2001-1-1',110,3
union all select '2001-1-2', 90,2
union all select '2001-1-3',200,5
union all select '2001-1-3',140,3
union all select '2001-1-5',150,1
go
--查询的存储过程
create proc p_qry
@prm_StatList nvarchar(1000), --统计列串
@prm_Conditon nvarchar(1000), --条件列串
@prm_StartDate datetime, --开始日期
@prm_EndDate datetime, --结束日期
@prm_StatType int
as
set nocount on
declare @s1 nvarchar(4000),@s nvarchar(4000),@tbname sysname,@dt datetime
select @tbname=quotename(N'##'+rtrim(newid())),
@s1=case
when isnull(@prm_Conditon,'')='' then 'where (Date1>=@dt1 and Date1<@dt2)'
else 'where ('+@prm_Conditon+') and (Date1>=@dt1 and Date1<@dt2)' end,
@s=N'insert '+@tbname+' select '+@prm_StatList+N' from MyTable '+@s1,
@s1=N'select id=identity(int,1,1),'+@prm_StatList+N' into '+@tbname+' from MyTable '+@s1,
@prm_StartDate=convert(char(10),@prm_StartDate,120),
@prm_EndDate=dateadd(day,1,convert(char(10),@prm_EndDate,120))
if @prm_StatType=0 --不知道全部统计是代表怎么统计?
begin
print '写全部统计的处理代码'
end
else if @prm_StatType between 1 and 4
begin
declare @sql nvarchar(4000)
select @dt=case
when @prm_StatType=1
then dateadd(day,1,@prm_StartDate)
when @prm_StatType=2
then dateadd(month,1,dateadd(day,1-day(@prm_StartDate),@prm_StartDate))
when @prm_StatType=3
then CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@prm_StartDate)*3+1,DATEADD(Month,-Month(@prm_StartDate),@prm_StartDate)),120)+'1'
when @prm_StatType=4
then dateadd(year,1,convert(char(5),@prm_StartDate,120)+'-1-1')
end,
@sql=N'
if @dt>@prm_EndDate set @dt=@prm_EndDate
exec sp_executesql @s1,N''@dt1 datetime,@dt2 datetime'',@prm_StartDate,@dt
while @dt<@prm_EndDate
begin
select @prm_StartDate=@dt,
@dt=case
when dateadd('
+substring('ddmmqqyy',@prm_StatType*2-1,2)
+N',1,@prm_StartDate)>@prm_EndDate
then @prm_EndDate
else dateadd('
+substring('ddmmqqyy',@prm_StatType*2-1,2)
+N',1,@prm_StartDate) end
exec sp_executesql @s,N''@dt1 datetime,@dt2 datetime'',@prm_StartDate,@dt
end'
exec sp_executesql @sql,
N'@s1 nvarchar(4000),@s nvarchar(4000),@prm_StartDate datetime,@prm_EndDate datetime,@dt datetime',
@s1,@s,@prm_StartDate,@prm_EndDate,@dt
exec('select * from '+@tbname+' order by id;drop table '+@tbname)
end
go
--调用
exec p_qry 'Value1_Sum=isnull(sum(Value1),0),Value10_Avg=isnull(Avg(Value10),0)',
'','2001-1-1','2001-12-5',2
go
--删除测试
drop table MyTable
drop proc p_qry
Top
9 楼kingbao（大宝）回复于 2005-04-19 15:33:31 得分 0
邹建兄,你好强.
虽然没有解决我的问题,但我看到了你的精神.我结了。Top
10 楼Hopewell_Go（好的在后頭﹗希望更好﹗﹗）回复于 2005-04-19 15:40:18 得分 0
在此只有幫樓主UP
Top
11 楼kingbao（大宝）回复于 2005-04-19 15:47:58 得分 0
我只想做循环把动态Sql执行的结构放入临时表,返回....Top
