Office中国论坛/Access中国论坛
标题:
浅谈数据库开发(五)——从宽表到存储过程(之四)
[打印本页]
作者:
roych
时间:
2019-11-16 16:00
标题:
浅谈数据库开发(五)——从宽表到存储过程(之四)
我们在前面已经写了一个存储过程,但很明显,我们的update语句挺多的,9个字段,就需要写个update语句,假若某天老板要看一个月的,岂不是要定义31个变量,然后更新31次?我们也没有更好的办法呢?答案是:有。
——写循环语句。
我们知道,这些update语句的格式都是相似的。理论上,我们可以把update那部分剥离出来,作为一个子过程来调用,写循环即可。但现实是,更新的字段不一致,这将涉及到SQL语句字符串的拼接。所以暂时我们不考虑用子过程的方式来完成这个任务,而是就地修改存储过程。
对于经验不太丰富的新手,在拼接SQL字符串时,个人建议先使用print的方法来检查语法是否正确。正确后,再进行正式的执行。
我们先来看看这些语句是结构:
update log_result set log_result.day0=1 from (select 用户ID from log_info where 登录时间=@day0) b
where log_result.userID=b.用户ID
复制代码
从语句中,我们知道a表中,字段day0可以作为一个循环变量(day0~day8),而b表中的@day0同样也可以作为一个循环变量(@day0~@day8),而其它的则无需更改。此外,day0和@day0原则上应该是一一对应的。因此,接下来,我们可以声明一个SQL字符串用于存放语句,声明一个@i用于循环赋值,并且初始化,右击,修改存储过程:
USE [test]
GO
/****** Object: StoredProcedure [dbo].[sp_user_count] Script Date: 2019-11-16 14:54:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Roych
-- Create date: 2019-11-16
-- Description: 用于完成用户留存率数据源的计算
-- =============================================
ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
AS
BEGIN
SET NOCOUNT ON;
--定义9个日期变量
declare @day0 date,@day1 date,@day2 date,@day3 date,@day4 date
,@day5 date,@day6 date,@day7 date,@day8 date
declare @SQL varchar(500)
declare @i int
/*
另一种变量声明的写法:
declare @day0 date
declare @day1 date
……………………
declare @dayn date
*/
--如果使用默认值(即无输入),则预设为系统日期前8天。
if @min_date is null
set @min_date=dateadd(day,-8,GETDATE())
set @day0=@min_date
set @day1=DATEADD(day,1,@min_date)
set @day2=DATEADD(day,2,@min_date)
set @day3=DATEADD(day,3,@min_date)
set @day4=DATEADD(day,4,@min_date)
set @day5=DATEADD(day,5,@min_date)
set @day6=DATEADD(day,6,@min_date)
set @day7=DATEADD(day,7,@min_date)
set @day8=DATEADD(day,8,@min_date)
--初始化
set @SQL=''
set @i=0
/*
--清空旧数据
truncate table log_result
--插入新数据(这里只插入day0~day8的数据,并初始化登录天数为0)
insert into log_result
select 用户ID,min(登录时间),0,0,0,0,0,0,0,0,0
from log_info where 登录时间 between @day0 and @day8
group by 用户ID
*/
set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
+'=1 from (select 用户ID from log_info where 登录时间='+convert(varchar(10),@day0,120)
+') b where log_result.userID=b.用户ID'
print(@SQL)
/*
--更新字段day0~day8
update log_result set log_result.day0=1 from (select 用户ID from log_info where 登录时间=@day0) b
where log_result.userID=b.用户ID
update log_result set log_result.day1=1 from (select 用户ID from log_info where 登录时间=@day1) b
where log_result.userID=b.用户ID
update log_result set log_result.day2=1 from (select 用户ID from log_info where 登录时间=@day2) b
where log_result.userID=b.用户ID
update log_result set log_result.day3=1 from (select 用户ID from log_info where 登录时间=@day3) b
where log_result.userID=b.用户ID
update log_result set log_result.day4=1 from (select 用户ID from log_info where 登录时间=@day4) b
where log_result.userID=b.用户ID
update log_result set log_result.day5=1 from (select 用户ID from log_info where 登录时间=@day5) b
where log_result.userID=b.用户ID
update log_result set log_result.day6=1 from (select 用户ID from log_info where 登录时间=@day6) b
where log_result.userID=b.用户ID
update log_result set log_result.day7=1 from (select 用户ID from log_info where 登录时间=@day7) b
where log_result.userID=b.用户ID
update log_result set log_result.day8=1 from (select 用户ID from log_info where 登录时间=@day8) b
where log_result.userID=b.用户ID
*/
END
复制代码
变量@SQL的具体长度,可以根据实际设置。我这里稍稍设置偏大一些。由于我们的目的是检查SQL语句,因此,可以先把前面的各种操作注释掉(truncate,insert和update等)。完成后点工具栏上的三角按钮“执行”。然后同样按前面提到的方法执行,结果生成了这样的语句:
[attach]63540[/attach]
我们留意到,这个并不是日期格式(日期格式是需要加上单引号的)。因此需要修改前面的语句为:
set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
+'=1 from (select 用户ID from log_info where 登录时间='''+convert(varchar(10),@day0,120)
+''') b where log_result.userID=b.用户ID'
复制代码
这是SQL语句中的用法。使用单引号时,是加一对的,请自行对比。这时候同样再执行一次,得到正确的结果。这里就不再截图了。
可能有人会问,如果本身就是字符串,还里面还有单引号怎么办?答案很简单,再加一对。——这种情况也是有的。在链接服务器上同步数据时,其中一个参数就是SQL字符串,以单引号括起来引用。如果需要传日期值给这个字符串,那么就得看加上两对(即4个单引号)了。详情请留意拙作:
巧用链接服务器同步数据
这里暂时不讨论这个问题。确实不懂的版友回复本帖后,我再贴答案。
至此,我们写了一个简单的SQL语句拼接,接下来,我们将它修改为while循环,当然这时候的@day0就不能直接使用了,而是应该改为dateadd了:
while @i<9
begin
set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
+' =1 from (select 用户ID from log_info where 登录时间='''+convert(varchar(10),DATEADD(day,@i,@min_date),120)
+''') b where log_result.userID=b.用户ID'
print(@SQL)
set @i=@i+1
end
复制代码
同样地,我们执行修改后,再来执行一下存储过程,于是得到以下结果:[attach]63541[/attach]
看起来,确实是我们想要的结果。
既然语句没问题了,那么,我们就可以把print语句改成exec了。删除不必要的变量和脚本之后,得到以下存储过程:
USE [test]
GO
/****** Object: StoredProcedure [dbo].[sp_user_count] Script Date: 2019-11-16 14:54:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Roych
-- Create date: 2019-11-16
-- Description: 用于完成用户留存率数据源的计算
-- =============================================
ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
AS
BEGIN
SET NOCOUNT ON;
--定义9个日期变量
declare @SQL varchar(500)
declare @i int
--如果使用默认值(即无输入),则预设为系统日期前8天。
if @min_date is null
set @min_date=dateadd(day,-8,GETDATE())
--初始化
set @SQL=''
set @i=0
--清空旧数据
truncate table log_result
--插入新数据(这里只插入day0~day8的数据,并初始化登录天数为0)
insert into log_result
select 用户ID,min(登录时间),0,0,0,0,0,0,0,0,0
from log_info where 登录时间 between @min_date and dateadd(day,8,@min_date)
group by 用户ID
--执行day0~day8的更新查询
while @i<9
begin
set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
+' from (select 用户ID from log_info where 登录时间='''+convert(varchar(10),DATEADD(day,@i,@min_date),120)
+''') b where log_result.userID=b.用户ID'
exec(@SQL)
set @i=@i+1
end
END
复制代码
这样,看起来是不是清爽多了?验证一下,看看结果如何:
[attach]63542[/attach]
看起来没什么问题。那么结果表就可以用这样写了:
[attach]63543[/attach]
稍稍对比下,看上去应该对得上。
[attach]63544[/attach]
至此,一个存储过程就开发完成了。学会了么?附上附件,供参考。
[attach]63545[/attach]
有什么问题,欢迎跟帖。
作者:
GOODWIN
时间:
2021-10-11 10:20
学习
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3