设为首页收藏本站Access中国

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

返回列表 发新帖
查看: 2254|回复: 1

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

[复制链接]
发表于 2019-11-16 16:00:38 | 显示全部楼层 |阅读模式
我们在前面已经写了一个存储过程,但很明显,我们的update语句挺多的,9个字段,就需要写个update语句,假若某天老板要看一个月的,岂不是要定义31个变量,然后更新31次?我们也没有更好的办法呢?答案是:有。
——写循环语句。

我们知道,这些update语句的格式都是相似的。理论上,我们可以把update那部分剥离出来,作为一个子过程来调用,写循环即可。但现实是,更新的字段不一致,这将涉及到SQL语句字符串的拼接。所以暂时我们不考虑用子过程的方式来完成这个任务,而是就地修改存储过程。
对于经验不太丰富的新手,在拼接SQL字符串时,个人建议先使用print的方法来检查语法是否正确。正确后,再进行正式的执行。
我们先来看看这些语句是结构:
  1. update log_result set log_result.day0=1 from (select 用户ID from log_info where 登录时间=@day0) b
  2. where log_result.userID=b.用户ID
复制代码
从语句中,我们知道a表中,字段day0可以作为一个循环变量(day0~day8),而b表中的@day0同样也可以作为一个循环变量(@day0~@day8),而其它的则无需更改。此外,day0和@day0原则上应该是一一对应的。因此,接下来,我们可以声明一个SQL字符串用于存放语句,声明一个@i用于循环赋值,并且初始化,右击,修改存储过程:
  1. USE [test]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[sp_user_count]    Script Date: 2019-11-16 14:54:37 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:                Roych
  10. -- Create date: 2019-11-16
  11. -- Description:        用于完成用户留存率数据源的计算
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
  14. AS
  15. BEGIN
  16.         SET NOCOUNT ON;
  17.         --定义9个日期变量
  18.         declare @day0 date,@day1 date,@day2 date,@day3 date,@day4 date
  19.                         ,@day5 date,@day6 date,@day7 date,@day8 date
  20.         declare @SQL varchar(500)
  21.         declare @i int
  22.         /*
  23.         另一种变量声明的写法:
  24.         declare @day0 date
  25.         declare @day1 date
  26.         ……………………
  27.         declare @dayn date
  28.         */
  29.         --如果使用默认值(即无输入),则预设为系统日期前8天。
  30.         if @min_date is null
  31.                 set @min_date=dateadd(day,-8,GETDATE())
  32.         set @day0=@min_date
  33.         set @day1=DATEADD(day,1,@min_date)
  34.         set @day2=DATEADD(day,2,@min_date)
  35.         set @day3=DATEADD(day,3,@min_date)
  36.         set @day4=DATEADD(day,4,@min_date)
  37.         set @day5=DATEADD(day,5,@min_date)
  38.         set @day6=DATEADD(day,6,@min_date)
  39.         set @day7=DATEADD(day,7,@min_date)
  40.         set @day8=DATEADD(day,8,@min_date)

  41.         --初始化
  42.         set @SQL=''
  43.         set @i=0
  44. /*
  45. --清空旧数据
  46. truncate table log_result
  47. --插入新数据(这里只插入day0~day8的数据,并初始化登录天数为0)
  48. insert into log_result
  49. select 用户ID,min(登录时间),0,0,0,0,0,0,0,0,0
  50. from log_info where 登录时间 between @day0 and @day8
  51. group by 用户ID
  52. */
  53. set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
  54.                  +'=1 from (select 用户ID from log_info where 登录时间='+convert(varchar(10),@day0,120)
  55.                  +') b where log_result.userID=b.用户ID'
  56. print(@SQL)

  57. /*
  58. --更新字段day0~day8
  59. update log_result set log_result.day0=1 from (select 用户ID from log_info where 登录时间=@day0) b
  60. where log_result.userID=b.用户ID

  61. update log_result set log_result.day1=1 from (select 用户ID from log_info where 登录时间=@day1) b
  62. where log_result.userID=b.用户ID

  63. update log_result set log_result.day2=1 from (select 用户ID from log_info where 登录时间=@day2) b
  64. where log_result.userID=b.用户ID

  65. update log_result set log_result.day3=1 from (select 用户ID from log_info where 登录时间=@day3) b
  66. where log_result.userID=b.用户ID

  67. update log_result set log_result.day4=1 from (select 用户ID from log_info where 登录时间=@day4) b
  68. where log_result.userID=b.用户ID

  69. update log_result set log_result.day5=1 from (select 用户ID from log_info where 登录时间=@day5) b
  70. where log_result.userID=b.用户ID

  71. update log_result set log_result.day6=1 from (select 用户ID from log_info where 登录时间=@day6) b
  72. where log_result.userID=b.用户ID

  73. update log_result set log_result.day7=1 from (select 用户ID from log_info where 登录时间=@day7) b
  74. where log_result.userID=b.用户ID

  75. update log_result set log_result.day8=1 from (select 用户ID from log_info where 登录时间=@day8) b
  76. where log_result.userID=b.用户ID
  77. */
  78. END
复制代码
变量@SQL的具体长度,可以根据实际设置。我这里稍稍设置偏大一些。由于我们的目的是检查SQL语句,因此,可以先把前面的各种操作注释掉(truncate,insert和update等)。完成后点工具栏上的三角按钮“执行”。然后同样按前面提到的方法执行,结果生成了这样的语句:

我们留意到,这个并不是日期格式(日期格式是需要加上单引号的)。因此需要修改前面的语句为:
  1. set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
  2.                  +'=1 from (select 用户ID from log_info where 登录时间='''+convert(varchar(10),@day0,120)
  3.                  +''') b where log_result.userID=b.用户ID'
复制代码
这是SQL语句中的用法。使用单引号时,是加一对的,请自行对比。这时候同样再执行一次,得到正确的结果。这里就不再截图了。
可能有人会问,如果本身就是字符串,还里面还有单引号怎么办?答案很简单,再加一对。——这种情况也是有的。在链接服务器上同步数据时,其中一个参数就是SQL字符串,以单引号括起来引用。如果需要传日期值给这个字符串,那么就得看加上两对(即4个单引号)了。详情请留意拙作:
巧用链接服务器同步数据
这里暂时不讨论这个问题。确实不懂的版友回复本帖后,我再贴答案。

至此,我们写了一个简单的SQL语句拼接,接下来,我们将它修改为while循环,当然这时候的@day0就不能直接使用了,而是应该改为dateadd了:
  1. while @i<9
  2. begin
  3. set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
  4.                  +' =1 from (select 用户ID from log_info where 登录时间='''+convert(varchar(10),DATEADD(day,@i,@min_date),120)
  5.                  +''') b where log_result.userID=b.用户ID'
  6.         print(@SQL)
  7. set @i=@i+1
  8. end
复制代码
同样地,我们执行修改后,再来执行一下存储过程,于是得到以下结果:
看起来,确实是我们想要的结果。
既然语句没问题了,那么,我们就可以把print语句改成exec了。删除不必要的变量和脚本之后,得到以下存储过程:
  1. USE [test]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[sp_user_count]    Script Date: 2019-11-16 14:54:37 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:                Roych
  10. -- Create date: 2019-11-16
  11. -- Description:        用于完成用户留存率数据源的计算
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
  14. AS
  15. BEGIN
  16.         SET NOCOUNT ON;
  17.         --定义9个日期变量
  18.         declare @SQL varchar(500)
  19.         declare @i int
  20.         --如果使用默认值(即无输入),则预设为系统日期前8天。
  21.         if @min_date is null
  22.                 set @min_date=dateadd(day,-8,GETDATE())
  23.         --初始化
  24.         set @SQL=''
  25.         set @i=0
  26. --清空旧数据
  27. truncate table log_result
  28. --插入新数据(这里只插入day0~day8的数据,并初始化登录天数为0)
  29. insert into log_result
  30. select 用户ID,min(登录时间),0,0,0,0,0,0,0,0,0
  31. from log_info where 登录时间 between @min_date and dateadd(day,8,@min_date)
  32. group by 用户ID
  33. --执行day0~day8的更新查询
  34. while @i<9
  35. begin
  36. set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
  37.                  +' from (select 用户ID from log_info where 登录时间='''+convert(varchar(10),DATEADD(day,@i,@min_date),120)
  38.                  +''') b where log_result.userID=b.用户ID'
  39.         exec(@SQL)
  40. set @i=@i+1
  41. end

  42. END
复制代码
这样,看起来是不是清爽多了?验证一下,看看结果如何:

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

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

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

有什么问题,欢迎跟帖。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册

x
发表于 2021-10-11 10:20:35 | 显示全部楼层

学习
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-3-29 03:42 , Processed in 0.094550 second(s), 27 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表