sql 游标和根据一个时间获取开始时间到结束时间的年月日

最新推荐文章于 2022-05-03 11:43:08 发布

原创最新推荐文章于 2022-05-03 11:43:08 发布 · 876 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #join

Sql 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一段SQL脚本的实现细节，包括使用T-SQL声明和操作游标来遍历日期范围内的数据记录，并执行复杂的聚合查询。此外，还展示了如何在循环中更新表数据并利用事务确保数据完整性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

declare @etime datetime,@stime datetime , @time varchar(20) set @etime='2011-03-15' set @stime='2011-01-01' declare mycur cursor for select convert(varchar(10),dateadd(month,number,@stime),120) from master..spt_values--------创建游标 where type='P' and dateadd(month,number,@stime)<=@etime open mycur--------------打开游标 fetch next from mycur into @time WHILE @@FETCH_STATUS = 0 BEGIN select sum(join_count) , sum(actual_count),sum(yxcj_count) from ( select join_count=(select count(groupbuy_join.join_id) from groupbuy_join inner join groupbuy_item on groupbuy_join.item_id=groupbuy_item.item_id where groupbuy_item.g_id=gg.g_id and groupbuy_join.del<>'1' and datepart(year,groupbuy_join.date)=datepart(year,@time) and datepart(month,groupbuy_join.date)=datepart(month,@time) ) ,actual_count=(select count(groupbuy_join.join_id) from groupbuy_join inner join groupbuy_item on groupbuy_join.item_id=groupbuy_item.item_id where groupbuy_item.g_id=gg.g_id and groupbuy_join.del<>'1' and datepart(year,groupbuy_join.date)=datepart(year,@time) and datepart(month,groupbuy_join.date)=datepart(month,@time) and empty<>1 and outAddress<>1 ) ,yxcj_count = (select count(groupbuy_join.join_id) from groupbuy_join inner join groupbuy_item on groupbuy_join.item_id = groupbuy_item.item_id where groupbuy_item.g_id = gg.g_id and groupbuy_join.del <> '1' and cjzk = '1' and datepart(year,groupbuy_join.date)=datepart(year,@time) and datepart(month,groupbuy_join.date)=datepart(month,@time) and groupbuy_join.website like '%亚讯车网%') from groupbuy gg inner join Thesaurus_cars_series on gg.topic=Thesaurus_cars_series.codeId inner join groupbuy_product gc on gg.pro_id=gc.pro_id inner join temp_groupbuy_principal on gc.man_id=temp_groupbuy_principal.gp_id where gg.del<>'1' and gp_id=30 ) as t fetch next from mycur into @time--------游标 END close mycur --关闭游标 DEALLOCATE mycur----删除游标

--------------------------------------------------------------------------------------------------------

create view sz_users_biz as select * from szcars2.dbo.u_users_biz begin transaction ----开始事务 declare @num int-----循环次数 set @num=0 declare @errorSum int,@curr_g_id int------创建错误变量 declare @users_text varchar(8000)-- varchar(max)--next--客户简介 declare @id int-- 商家表id declare mycur cursor for select ub_id from sz_users_biz--------创建游标 open mycur fetch next from mycur into @id WHILE @@FETCH_STATUS = 0 BEGIN select @users_text=ub_desc from sz_users_biz where ub_id=@id update u_users_biz set ub_desc=@users_text where ub_id=@id set @errorSum=@errorSum+@@error --累计是否有错 set @num=@num+1 fetch next from mycur into @id--------游标 END close mycur --关闭游标 DEALLOCATE mycur----删除游标 if @errorSum>0 BEGIN rollback transaction END else BEGIN commit transaction END print(@num)