sql server存储过程实例_查询相邻时间段数据统计

作者在 2017-07-04 17:50:10 发布以下内容
一、sql脚本
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE shijianduan (@canshu varchar(100))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE shijian_cur CURSOR 
	for select riqi from ceshi where month(riqi) = month(@canshu)
	DECLARE @shijian nvarchar(2000)
	OPEN shijian_cur
	FETCH NEXT FROM shijian_cur into @shijian
		WHILE @@FETCH_STATUS = 0
	BEGIN
		select SUM(shuju) from ceshi where riqi between '2017-07-01' and @shijian and MONTH(riqi) = MONTH(@canshu)
		FETCH NEXT FROM shijian_cur into @shijian
	end
    -- Insert statements for procedure here
    CLOSE shijian_cur
	--释放资源
	DEALLOCATE shijian_cur
END
GO
二、ceshi表结构及数据 
insert into ceshi(riqi,shuju) values('2017-07-01',11);
insert into ceshi(riqi,shuju) values('2017-06-01','11');
insert into ceshi(riqi,shuju) values('2017-07-03',33);
insert into ceshi(riqi,shuju) values('2017-07-04',44);
三、功能说明:传参查询指定月份中累计到相邻日期内的数据,解释(7月1号,统计当天的数据;
7月2号统计1号到2号的数据;7月3号统计1号到3号的数据,一次类推)
四、sql_server 存储过程的调用 
exec shijianduan '2017-07-01'
sql_server | 阅读 2065 次
文章评论,共0条
游客请输入验证码