浅谈数据库开发（五）——从宽表到存储过程（之四）

roych · 发表于 2019-11-16 16:00:38

我们在前面已经写了一个存储过程，但很明显，我们的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等）。完成后点工具栏上的三角按钮“执行”。然后同样按前面提到的方法执行，结果生成了这样的语句：

我们留意到，这个并不是日期格式（日期格式是需要加上单引号的）。因此需要修改前面的语句为：

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

复制代码

同样地，我们执行修改后，再来执行一下存储过程，于是得到以下结果：
看起来，确实是我们想要的结果。
既然语句没问题了，那么，我们就可以把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

复制代码

这样，看起来是不是清爽多了？验证一下，看看结果如何：

看起来没什么问题。那么结果表就可以用这样写了：

稍稍对比下，看上去应该对得上。

至此，一个存储过程就开发完成了。学会了么？附上附件，供参考。

有什么问题，欢迎跟帖。

GOODWIN · 发表于 2021-10-11 10:20:35

学习

帐号		自动登录	找回密码
密码			注册

主题相关群

地区相关群[您还没设置居住地]

浅谈数据库开发（五）——从宽表到存储过程（之四）

本帖子中包含更多资源

群介绍
主题无相 (随机显示版	同地区群块其他地区)

群介绍
Access免费培训群2 (792054000)