游标实例2

2023-10-04 09:55:16

问题描述：

最后输出结果

分析：这个题其实可以不用游标做的，用俩个相同表join日期关联后再进行分组就好，但同样也可以试试游标，写起来也会简介一些

根据题目的数据创建表：

CREATE TABLE #table_a (
event_date date NOT NULL,
user_id varchar(6) NOT NULL,
PRIMARY KEY (event_date,user_id));
INSERT INTO #table_a VALUES('2021-01-01','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-01','4008is');
INSERT INTO #table_a VALUES('2021-01-01','AKt4W1');
INSERT INTO #table_a VALUES('2021-01-01','4e105q');
INSERT INTO #table_a VALUES('2021-01-02','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-02','s4R510');
INSERT INTO #table_a VALUES('2021-01-03','N2p51U');
INSERT INTO #table_a VALUES('2021-01-03','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-03','4008is');
INSERT INTO #table_a VALUES('2021-01-04','M7hkg7');
INSERT INTO #table_a VALUES('2021-01-04','s43G14');
INSERT INTO #table_a VALUES('2021-01-05','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-05','846h0S');
INSERT INTO #table_a VALUES('2021-01-05','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-05','4008is');
INSERT INTO #table_a VALUES('2021-01-06','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-06','846h0S');
INSERT INTO #table_a VALUES('2021-01-06','Q17Lo8');
INSERT INTO #table_a VALUES('2021-01-07','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-07','4008is');
INSERT INTO #table_a VALUES('2021-01-07','s4R510');
INSERT INTO #table_a VALUES('2021-01-08','846h0S');
INSERT INTO #table_a VALUES('2021-01-08','Q17Lo8');
INSERT INTO #table_a VALUES('2021-01-09','M7hkg7');
INSERT INTO #table_a VALUES('2021-01-09','s43G14');
INSERT INTO #table_a VALUES('2021-01-10','06Uc67');
INSERT INTO #table_a VALUES('2021-01-10','AKt4W1');
INSERT INTO #table_a VALUES('2021-01-10','4e105q');
INSERT INTO #table_a VALUES('2021-01-11','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-12','4e105q');
INSERT INTO #table_a VALUES('2021-01-12','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-12','846h0S');
INSERT INTO #table_a VALUES('2021-01-12','Q17Lo8');
INSERT INTO #table_a VALUES('2021-01-13','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-13','4008is');
INSERT INTO #table_a VALUES('2021-01-13','s4R510');
INSERT INTO #table_a VALUES('2021-01-14','N2p51U');
INSERT INTO #table_a VALUES('2021-01-14','06Uc67');
INSERT INTO #table_a VALUES('2021-01-15','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-15','4008is');
INSERT INTO #table_a VALUES('2021-01-15','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-16','846h0S');
INSERT INTO #table_a VALUES('2021-01-16','Q17Lo8');
INSERT INTO #table_a VALUES('2021-01-17','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-17','2223gf');
INSERT INTO #table_a VALUES('2021-01-17','28wvno');
INSERT INTO #table_a VALUES('2021-01-17','3526Ya');
INSERT INTO #table_a VALUES('2021-01-18','87l2L2');
INSERT INTO #table_a VALUES('2021-01-18','66JK3E');
INSERT INTO #table_a VALUES('2021-01-19','s4R510');
INSERT INTO #table_a VALUES('2021-01-19','846h0S');
INSERT INTO #table_a VALUES('2021-01-19','Q17Lo8');
INSERT INTO #table_a VALUES('2021-01-20','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-20','846h0S');
INSERT INTO #table_a VALUES('2021-01-21','Q17Lo8');
INSERT INTO #table_a VALUES('2021-01-21','7bTCDm');
INSERT INTO #table_a VALUES('2021-01-21','4008is');
INSERT INTO #table_a VALUES('2021-01-22','s4R510');
INSERT INTO #table_a VALUES('2021-01-22','N2p51U');
INSERT INTO #table_a VALUES('2021-01-22','2223gf');
INSERT INTO #table_a VALUES('2021-01-23','28wvno');
INSERT INTO #table_a VALUES('2021-01-23','87l2L2');
INSERT INTO #table_a VALUES('2021-01-24','66JK3E');
INSERT INTO #table_a VALUES('2021-01-24','AKt4W1');
INSERT INTO #table_a VALUES('2021-01-25','4e105q');
INSERT INTO #table_a VALUES('2021-01-26','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-26','846h0S');
INSERT INTO #table_a VALUES('2021-01-26','4e105q');
INSERT INTO #table_a VALUES('2021-01-27','i2a0LS');
INSERT INTO #table_a VALUES('2021-01-27','6W0HHu');
INSERT INTO #table_a VALUES('2021-01-27','1L68I8');
INSERT INTO #table_a VALUES('2021-01-27','47Elop');
INSERT INTO #table_a VALUES('2021-01-28','gAG18G');
INSERT INTO #table_a VALUES('2021-01-28','66JK3E');
INSERT INTO #table_a VALUES('2021-01-28','AKt4W1');

代码：


declare @date_start date
declare @date_end date
declare @date_max date set @date_max = (select max(event_date) from #table_a)
--定义游标名称及传参变量
declare cursor1 cursor for
select event_date from #table_a group by event_date order by event_date
open cursor1
	fetch next from cursor1 into @date_start 
		WHILE(@@FETCH_STATUS=0)
			BEGIN
				--给@date_end传参
				set @date_end=(select distinct dateadd(day,6,event_date) from #table_a where event_date=@date_start)

				select t.date_start,case when @date_max>t.date_end then t.date_end else @date_max end date_end,sum(t.num) num into #table from (select @date_start date_start,@date_end date_end,1 num from #table_a where event_date between @date_start and @date_end group by user_id)t group by t.date_start,t.date_end
				--游标向下一个
				FETCH NEXT FROM cursor1 INTO @date_start
				select * from #table
				drop table #table
			END			
	CLOSE cursor1
DEALLOCATE	cursor1

结果

这是sqlserver环境的临时表，换成mysql、oracle的普通表就可以直接执行

码农公寓

相关文章