Question

MSSQL 写一个存储过程按时间段进行查询

主要查询 信息表 tc_data表，
table tc_data ( ID numeric(22) -- ID号
, char_name nvarcher(64) -- 订单名称
, num numeric(22,3) -- 数量
, money numeric(18,22) -- 金额
, create_time datetime -- 时间
)
写一个存储过程参数是 条件（@condition nvarchar(64)），开始时间（@begintime nvarchar(64)），结束时间（@endtime nvarchar(64)）;
说明：
条件（@condition nvarchar(64)）的内容为：Year、Quarter、Month、Week
开始时间（@begintime nvarchar(64)）和 结束时间（@endtime nvarchar(64)）的内容为YYYY-MM-DD;
查询时，当条件为Year年时，显示年的最后一天的记录，按开始时间和结束时间来确定查询哪年，如果跨两个或两个以上年则显示各年最后一天的记录；
当条件为Quarter季度时，显示季度的最后一天的记录，按开始时间和结束时间来确定查询哪个季度，如果跨两个或两个以上的季度则显示各季度最后一天的记录；
当条件为Month月时，显示月最后一天的记录，按开始时间和结束时间来确定查询哪个月，如果跨两个或两个以上的月则显示各月最后一天的记录；
当条件为Week周时，显示周内每天的记录，按开始时间和结束时间来确定查询哪个周，如果跨两个或两个以上的周则最多显示四周每天的记录。

Answer 1

/*
主要查询 信息表 tc_data表，
create table tc_data ( ID numeric(22)   -- ID号
, char_name nvarcher(64)   -- 订单名称
, num numeric(22,3)   -- 数量
, money numeric(18,22)   -- 金额 
, create_time datetime   -- 时间
)
写一个存储过程参数是 条件（@condition nvarchar(64)），开始时间（@begintime nvarchar(64)），结束时间（@endtime nvarchar(64)）;
说明：
条件（@condition nvarchar(64)）的内容为：Year、Quarter、Month、Week
开始时间（@begintime nvarchar(64)）和 结束时间（@endtime nvarchar(64)）的内容为YYYY-MM-DD;
查询时，当条件为Year年时，显示年的最后一天的记录，按开始时间和结束时间来确定查询哪年，如果跨两个或两个以上年则显示各年最后一天的记录；
当条件为Quarter季度时，显示季度的最后一天的记录，按开始时间和结束时间来确定查询哪个季度，如果跨两个或两个以上的季度则显示各季度最后一天的记录；
当条件为Month月时，显示月最后一天的记录，按开始时间和结束时间来确定查询哪个月，如果跨两个或两个以上的月则显示各月最后一天的记录；
当条件为Week周时，显示周内每天的记录，按开始时间和结束时间来确定查询哪个周，如果跨两个或两个以上的周则最多显示四周每天的记录。
*/
--以下为SQL2005或更高版本，在SQL2000上会出错的
create proc dbo.Usp_Getdata
(
@condition as nvarchar(64),--条件
@begintime as nvarchar(64),--开始时间
@endtime as nvarchar(64)--结束时间
)
as
begin
	set nocount on;
	if datediff(dd,@begintime,@endtime)<0
	begin
		raiserror 50001 '@begintime必须小于@endtime'
		return
	end
	create table # (begintime datetime not null,endtime datetime not null)
	declare @Tcount as int
	if @condition='Year'
	begin
		insert into #
		select
			cast(tempYear as varchar(4))+'-12-31',cast(tempYear as varchar(4))+'-12-31 23:59:59.997'
		from (select top (datediff(yy,@begintime,@endtime)+1) ((row_number() over(order by ID))-1)+Year(@begintime) tempYear  from syscolumns) a
	end
	declare @tempbegin as int
	if @condition='Quarter'
	begin
		set @tempbegin=case when Month(@begintime)>=1 and Month(@begintime)<4 then 3 when Month(@begintime)>=4 and Month(@begintime)<7 then 6 when Month(@begintime)>=7 and Month(@begintime)<10 then 9 else 12 end
		insert into #
		select
			dateadd(m,tempNum*3,dateadd(dd,-1,dateadd(mm,1,cast((cast(Year(@begintime) as varchar(4))+'-'+cast(@tempbegin as varchar(2))+'-1') as datetime)))),dateadd(m,tempNum*3,dateadd(dd,-1,dateadd(mm,1,cast((cast(Year(@begintime) as varchar(4))+'-'+cast(@tempbegin as varchar(2))+'-1 23:59:59.997') as datetime))))
		from (select top (datediff(qq,@begintime,@endtime)+1) ((row_number() over(order by ID))-1) tempNum  from syscolumns) a
	end
	if @condition='Month'
	begin
		set @tempbegin=Month(@begintime)
		insert into #
		select
			dateadd(m,tempNum,dateadd(dd,-1,dateadd(mm,1,cast((cast(Year(@begintime) as varchar(4))+'-'+cast(@tempbegin as varchar(2))+'-1') as datetime)))),dateadd(m,tempNum,dateadd(dd,-1,dateadd(mm,1,cast((cast(Year(@begintime) as varchar(4))+'-'+cast(@tempbegin as varchar(2))+'-1 23:59:59.997') as datetime))))
		from (select top (datediff(mm,@begintime,@endtime)+1) ((row_number() over(order by ID))-1) tempNum  from syscolumns) a
	end
	if @condition='Week'
	begin
		declare @tempbegintime as datetime
		SET DATEFIRST 1;
		select @tempbegintime=dateadd(dd,(Datepart(wk,@begintime)-1)*7-Datepart(dw,cast(Year(@begintime) as varchar(4))+'-1-1')+1,cast(Year(@begintime) as varchar(4))+'-1-1')
		--insert into #
		select
			dateadd(dd,0,@tempbegintime),convert(varchar(10),dateadd(dd,(case when (datediff(wk,@begintime,@endtime)+1)>4 then 4*7 else (Datepart(wk,@endtime)-Datepart(wk,@begintime)+1)*7 end),@tempbegintime),120)+' 23:59:59.997'
	end	
	select a.* from tc_data a inner join # b on a.create_time between b.begintime and b.endtime
	drop table #
end
go
/*
--测试
exec dbo.Usp_Getdata 'Year','2014-01-01','2014-01-01';
exec dbo.Usp_Getdata 'Quarter','2014-01-01','2015-01-01';
exec dbo.Usp_Getdata 'Month','2014-01-01','2015-01-01';
exec dbo.Usp_Getdata 'Week','2014-06-15','2015-01-01';
exec dbo.Usp_Getdata 'Week','2014-06-15','2014-06-28';
exec dbo.Usp_Getdata 'Week','2014-01-01','2015-01-01';
*/

追问

哥们不行啊！
而且最好别用临时表，我是要想做多人查询用的，即可以给很多人进行数据查询，如果使用临时表能容易出问题的！
而且我做了测试不能用啊！

追答

做不了测试，那你怎么证明我写的就是错的呢？
不用临时表的话，那你就把临时表封装为一个字查询

追问

我的意思是

1. 如按年查询，给出每年12个月，每月最后的一次记录，不一定是每年每月后一天，有可能最后一天没有记录，就显示最后一次的记录；

2. 同理，按月查询，给出每月4周，每周最后的一次记录；

3. 按周查询，给出每周7天的记录；

追答

你说的需求又有变化，没工夫写了。你自己理解思路去吧

追问

谢谢！
问题我已经自己解决了！
做group by 做分组

追答

负责人的告诉你，临时表用好了，会给你的存储过程提速不少。用的时候要分场景，掌握好分寸，若在加上合理的索引，百万行级的数据表查询也是秒出结果