直接在邹老大写的prc基础上修改:---------------------------------------------------------------------
--测试数据
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 @d intset @d=datediff(day,@prm_StartDate,@prm_EndDate)+1
if isnull(@d,0)<1 return
set @prm_Conditon=case
when isnull(@prm_Conditon,'')='' then ''
else 'where ('+@prm_Conditon+')' end
set rowcount @d
select id=identity(int,0,1) into # from syscolumns a,syscolumns b
set rowcount 0
declare @s nvarchar(4000)
set @s=N'select Date1=convert(char('+case @prm_StatType
when 0 then N'10'
when 1 then N'10'
when 2 then N'7'
when 3 then N'4'
end
+'),@prm_StartDate+a.id,120),'
+@prm_StatList
+N' from # a left join MyTable b on (@prm_StartDate+a.id)=b.Date1
group by convert(char('+case @prm_StatType
when 0 then N'10'
when 1 then N'10'
when 2 then N'7'
when 3 then N'4'
end
+'),@prm_StartDate+a.id,120)'
exec sp_executesql @s,N'@prm_StartDate datetime,@prm_EndDate datetime'
,@prm_StartDate,@prm_EndDate
go--调用
exec p_qry 'Value1_Sum=isnull(sum(Value1),0),Value10_Avg=isnull(Avg(Value10),0)',
'','2001-1-1','2001-1-5',0
go
--测试数据
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 @d intset @d=datediff(day,@prm_StartDate,@prm_EndDate)+1
if isnull(@d,0)<1 return
set @prm_Conditon=case
when isnull(@prm_Conditon,'')='' then ''
else 'where ('+@prm_Conditon+')' end
set rowcount @d
select id=identity(int,0,1) into # from syscolumns a,syscolumns b
set rowcount 0
declare @s nvarchar(4000)
set @s=N'select Date1=convert(char('+case @prm_StatType
when 0 then N'10'
when 1 then N'10'
when 2 then N'7'
when 3 then N'4'
end
+'),@prm_StartDate+a.id,120),'
+@prm_StatList
+N' from # a left join MyTable b on (@prm_StartDate+a.id)=b.Date1
group by convert(char('+case @prm_StatType
when 0 then N'10'
when 1 then N'10'
when 2 then N'7'
when 3 then N'4'
end
+'),@prm_StartDate+a.id,120)'
exec sp_executesql @s,N'@prm_StartDate datetime,@prm_EndDate datetime'
,@prm_StartDate,@prm_EndDate
go--调用
exec p_qry 'Value1_Sum=isnull(sum(Value1),0),Value10_Avg=isnull(Avg(Value10),0)',
'','2001-1-1','2001-1-5',0
go
-----假如你想動態加條件語句,就不用這麼麻煩 定義這麼多變量了
@prm_StatList --统计列串
@prm_Conditon --条件列串 ---條件
@prm_StartDate --开始日期 ---條件才用到
@prm_EndDate --结束日期 ----條件才用到
@prm_StatType --统计类型[0:全部统计, 1:按日统计, 2:按月统计, 3:按年统计] ----條件才用到
我覺樓主想實現的完整功能就隻需要
@prm_StatList --统计列串
@prm_Conditon --条件列串 ---條件
exec('select '+ @prm_StatList +' from ' @prm_Conditon )