--生成测试数据
create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2, 55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 03:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'
go
--创建存储过程
create procedure sp_getInfo(@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 姓名'
--提取被统计时间范围内的数据,并插入临时表#t
select * into #t from t_a where dateadd(hh,24-@time,日期) between convert(char(8),dateadd(mm,-1,getdate()),120)+'21 00:00:00' and convert(char(8),getdate(),120)+'20 23:59:59'
--定义游标,获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(日期) from t_a order by day(日期)
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(日期)='+rtrim(@day)+' and 类别='''+类别+''' then 数量*单价 else 0 end) as int)'
from (select distinct 类别 from t_a) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(日期)='+rtrim(@day)+' then 数量*单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 姓名'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
go
--调用存储过程,输出结果楼主自己看
exec sp_getinfo 15
go
--清除测试环境
drop procedure sp_getinfo
drop table t_a
create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2, 55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 03:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'
go
--创建存储过程
create procedure sp_getInfo(@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 姓名'
--提取被统计时间范围内的数据,并插入临时表#t
select * into #t from t_a where dateadd(hh,24-@time,日期) between convert(char(8),dateadd(mm,-1,getdate()),120)+'21 00:00:00' and convert(char(8),getdate(),120)+'20 23:59:59'
--定义游标,获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(日期) from t_a order by day(日期)
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(日期)='+rtrim(@day)+' and 类别='''+类别+''' then 数量*单价 else 0 end) as int)'
from (select distinct 类别 from t_a) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(日期)='+rtrim(@day)+' then 数量*单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 姓名'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
go
--调用存储过程,输出结果楼主自己看
exec sp_getinfo 15
go
--清除测试环境
drop procedure sp_getinfo
drop table t_a
--------------------------------------------------------------------------------------------------------------------------------生成测试数据
create table t_a(姓名 varchar(6),单号 varchar(9),数量 money,单价 money,日期 datetime,类别 varchar(4))
insert t_a
select '张大夫','200510112',2, 55,'2006-01-01 12:30:30','中药' union all
select '李大夫','200601111',1,100,'2006-01-01 13:33:30','西药' union all
select '李大夫','200601112',1,100,'2006-01-02 16:33:30','草药' union all
select '王大夫','200601114',4,100,'2006-01-01 03:33:30','西药' union all
select '张大夫','200601115',1,400,'2006-01-03 11:33:30','中药' union all
select '李大夫','200601114',1,100,'2006-01-08 13:33:30','西药' union all
select '李大夫','200601155',1,100,'2006-01-09 13:33:30','西药' union all
select '李大夫','200601145',1,100,'2006-01-01 13:33:30','西药'
go
--创建存储过程
create procedure sp_getInfo(@month datetime,@time as int=0) --@time,统计时间中小时的限定值
as
begin
--输入参数合法性判定
if @time<0 or @time>23
return
declare @s varchar(8000),@day int
set @s='select 姓名'
--提取被统计时间范围内的数据,并插入临时表#t
select * into #t from t_a where dateadd(hh,24-@time,日期) between convert(char(8),dateadd(mm,-1,@month),120)+'21 00:00:00' and convert(char(8),@month,120)+'20 23:59:59'
--定义游标,获取当前统计范围内不重复的日期数据信息
declare t_cursor cursor for select distinct day(日期) from t_a order by day(日期)
--打开游标
open t_cursor
fetch next from t_cursor into @day
--遍历游标,利用动态SQL实现交叉表查询
while @@fetch_status=0
begin
select @s=@s+',['+rtrim(@day)+'号'+类别+']=cast(sum(case when day(日期)='+rtrim(@day)+' and 类别='''+类别+''' then 数量*单价 else 0 end) as int)'
from (select distinct 类别 from t_a) a
set @s=@s+',['+rtrim(@day)+'号合计]=cast(sum(case when day(日期)='+rtrim(@day)+' then 数量*单价 else 0 end) as int)'
fetch next from t_cursor into @day
end
--关闭游标
close t_cursor
deallocate t_cursor
--完成动态SQL语句的组织
set @s=@s+' from #t group by 姓名'
--输出动态SQL语句的内容
print @s
--执行动态SQL
exec(@s)
end
go
--调用存储过程,输出结果楼主自己看
exec sp_getinfo '2006-01-01',15
go
--清除测试环境
drop procedure sp_getinfo
drop table t_a