分段+动态列统计的存储过程-完整描述

Question

茅盾,既然有聚合函数
Sum(Value1), Avg(Value10), Sum(Value11)那不group by ,这些聚集函数如何计算?其实有group by 一直就是因为聚合函数的问题,如果你不要group by ,就不要聚集函数,如果你要聚集函数,就要group by ,除非只有聚集函数的列

Answer 1

比如这样啊  zjcxc(邹建) :
SELECT Sum(Value1), Avg(Value10), Sum(Value11) FROM MyTable WHERE Value1>100 AND Value10>1

Answer 2

--测试数据
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

Answer 3

--简化一下--测试数据
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

Answer 4

我只想做循环把动态Sql执行的结构放入临时表,返回....