Office中國(guó)論壇/Access中國(guó)論壇

標(biāo)題: 淺談數(shù)據(jù)庫(kù)開(kāi)發(fā)（五）——從寬表到存儲(chǔ)過(guò)程（之四） [打印本頁(yè)]
作者: roych    時(shí)間: 2019-11-16 16:00
標(biāo)題: 淺談數(shù)據(jù)庫(kù)開(kāi)發(fā)（五）——從寬表到存儲(chǔ)過(guò)程（之四）
我們?cè)谇懊嬉呀?jīng)寫(xiě)了一個(gè)存儲(chǔ)過(guò)程，但很明顯，我們的update語(yǔ)句挺多的，9個(gè)字段，就需要寫(xiě)個(gè)update語(yǔ)句，假若某天老板要看一個(gè)月的，豈不是要定義31個(gè)變量，然后更新31次？我們也沒(méi)有更好的辦法呢？答案是：有。
——寫(xiě)循環(huán)語(yǔ)句。

我們知道，這些update語(yǔ)句的格式都是相似的。理論上，我們可以把update那部分剝離出來(lái)，作為一個(gè)子過(guò)程來(lái)調(diào)用，寫(xiě)循環(huán)即可。但現(xiàn)實(shí)是，更新的字段不一致，這將涉及到SQL語(yǔ)句字符串的拼接。所以暫時(shí)我們不考慮用子過(guò)程的方式來(lái)完成這個(gè)任務(wù)，而是就地修改存儲(chǔ)過(guò)程。
對(duì)于經(jīng)驗(yàn)不太豐富的新手，在拼接SQL字符串時(shí)，個(gè)人建議先使用print的方法來(lái)檢查語(yǔ)法是否正確。正確后，再進(jìn)行正式的執(zhí)行。
我們先來(lái)看看這些語(yǔ)句是結(jié)構(gòu)：
  1. update log_result set log_result.day0=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day0) b
  2. where log_result.userID=b.用戶ID
復(fù)制代碼
從語(yǔ)句中，我們知道a表中，字段day0可以作為一個(gè)循環(huán)變量（day0~day8），而b表中的@day0同樣也可以作為一個(gè)循環(huán)變量（@day0~@day8），而其它的則無(wú)需更改。此外，day0和@day0原則上應(yīng)該是一一對(duì)應(yīng)的。因此，接下來(lái)，我們可以聲明一個(gè)SQL字符串用于存放語(yǔ)句，聲明一個(gè)@i用于循環(huán)賦值，并且初始化，右擊，修改存儲(chǔ)過(guò)程：
  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:        用于完成用戶留存率數(shù)據(jù)源的計(jì)算
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
  14. AS
  15. BEGIN
  16.         SET NOCOUNT ON;
  17.         --定義9個(gè)日期變量
  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.         另一種變量聲明的寫(xiě)法：
  24.         declare @day0 date
  25.         declare @day1 date
  26.         ……………………
  27.         declare @dayn date
  28.         */
  29.         --如果使用默認(rèn)值（即無(wú)輸入），則預(yù)設(shè)為系統(tǒng)日期前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)

  42.         --初始化
  43.         set @SQL=''
  44.         set @i=0
  45. /*
  46. --清空舊數(shù)據(jù)
  47. truncate table log_result
  48. --插入新數(shù)據(jù)（這里只插入day0~day8的數(shù)據(jù)，并初始化登錄天數(shù)為0）
  49. insert into log_result
  50. select 用戶ID,min(登錄時(shí)間),0,0,0,0,0,0,0,0,0
  51. from log_info where 登錄時(shí)間 between @day0 and @day8
  52. group by 用戶ID
  53. */
  54. set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
  55.                  +'=1 from (select 用戶ID from log_info where 登錄時(shí)間='+convert(varchar(10),@day0,120)
  56.                  +') b where log_result.userID=b.用戶ID'
  57. print(@SQL)

  59. /*
  60. --更新字段day0~day8
  61. update log_result set log_result.day0=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day0) b
  62. where log_result.userID=b.用戶ID

  64. update log_result set log_result.day1=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day1) b
  65. where log_result.userID=b.用戶ID

  67. update log_result set log_result.day2=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day2) b
  68. where log_result.userID=b.用戶ID

  70. update log_result set log_result.day3=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day3) b
  71. where log_result.userID=b.用戶ID

  73. update log_result set log_result.day4=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day4) b
  74. where log_result.userID=b.用戶ID

  76. update log_result set log_result.day5=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day5) b
  77. where log_result.userID=b.用戶ID

  79. update log_result set log_result.day6=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day6) b
  80. where log_result.userID=b.用戶ID

  82. update log_result set log_result.day7=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day7) b
  83. where log_result.userID=b.用戶ID

  85. update log_result set log_result.day8=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day8) b
  86. where log_result.userID=b.用戶ID
  87. */
  88. END
復(fù)制代碼
變量@SQL的具體長(zhǎng)度，可以根據(jù)實(shí)際設(shè)置。我這里稍稍設(shè)置偏大一些。由于我們的目的是檢查SQL語(yǔ)句，因此，可以先把前面的各種操作注釋掉（truncate，insert和update等）。完成后點(diǎn)工具欄上的三角按鈕“執(zhí)行”。然后同樣按前面提到的方法執(zhí)行，結(jié)果生成了這樣的語(yǔ)句：
[attach]63540[/attach]
我們留意到，這個(gè)并不是日期格式（日期格式是需要加上單引號(hào)的）。因此需要修改前面的語(yǔ)句為：
  1. set @SQL='update log_result set log_result.day'+CONVERT(varchar(2),@i)
  2.                  +'=1 from (select 用戶ID from log_info where 登錄時(shí)間='''+convert(varchar(10),@day0,120)
  3.                  +''') b where log_result.userID=b.用戶ID'
復(fù)制代碼
這是SQL語(yǔ)句中的用法。使用單引號(hào)時(shí)，是加一對(duì)的，請(qǐng)自行對(duì)比。這時(shí)候同樣再執(zhí)行一次，得到正確的結(jié)果。這里就不再截圖了。
可能有人會(huì)問(wèn)，如果本身就是字符串，還里面還有單引號(hào)怎么辦？答案很簡(jiǎn)單，再加一對(duì)�！@種情況也是有的。在鏈接服務(wù)器上同步數(shù)據(jù)時(shí)，其中一個(gè)參數(shù)就是SQL字符串，以單引號(hào)括起來(lái)引用。如果需要傳日期值給這個(gè)字符串，那么就得看加上兩對(duì)（即4個(gè)單引號(hào)）了。詳情請(qǐng)留意拙作：
巧用鏈接服務(wù)器同步數(shù)據(jù)
這里暫時(shí)不討論這個(gè)問(wèn)題。確實(shí)不懂的版友回復(fù)本帖后，我再貼答案。

至此，我們寫(xiě)了一個(gè)簡(jiǎn)單的SQL語(yǔ)句拼接，接下來(lái)，我們將它修改為while循環(huán)，當(dāng)然這時(shí)候的@day0就不能直接使用了，而是應(yīng)該改為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 登錄時(shí)間='''+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
復(fù)制代碼
同樣地，我們執(zhí)行修改后，再來(lái)執(zhí)行一下存儲(chǔ)過(guò)程，于是得到以下結(jié)果：[attach]63541[/attach]
看起來(lái)，確實(shí)是我們想要的結(jié)果。
既然語(yǔ)句沒(méi)問(wèn)題了，那么，我們就可以把print語(yǔ)句改成exec了。刪除不必要的變量和腳本之后，得到以下存儲(chǔ)過(guò)程：
  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:        用于完成用戶留存率數(shù)據(jù)源的計(jì)算
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
  14. AS
  15. BEGIN
  16.         SET NOCOUNT ON;
  17.         --定義9個(gè)日期變量
  18.         declare @SQL varchar(500)
  19.         declare @i int
  20.         --如果使用默認(rèn)值（即無(wú)輸入），則預(yù)設(shè)為系統(tǒng)日期前8天。
  21.         if @min_date is null
  22.                 set @min_date=dateadd(day,-8,GETDATE())
  23.         --初始化
  24.         set @SQL=''
  25.         set @i=0
  26. --清空舊數(shù)據(jù)
  27. truncate table log_result
  28. --插入新數(shù)據(jù)（這里只插入day0~day8的數(shù)據(jù)，并初始化登錄天數(shù)為0）
  29. insert into log_result
  30. select 用戶ID,min(登錄時(shí)間),0,0,0,0,0,0,0,0,0
  31. from log_info where 登錄時(shí)間 between @min_date and dateadd(day,8,@min_date)
  32. group by 用戶ID
  33. --執(zhí)行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 登錄時(shí)間='''+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

  43. END
復(fù)制代碼
這樣，看起來(lái)是不是清爽多了？驗(yàn)證一下，看看結(jié)果如何：
[attach]63542[/attach]
看起來(lái)沒(méi)什么問(wèn)題。那么結(jié)果表就可以用這樣寫(xiě)了：
[attach]63543[/attach]
稍稍對(duì)比下，看上去應(yīng)該對(duì)得上。
[attach]63544[/attach]
至此，一個(gè)存儲(chǔ)過(guò)程就開(kāi)發(fā)完成了。學(xué)會(huì)了么？附上附件，供參考。
[attach]63545[/attach]
有什么問(wèn)題，歡迎跟帖。
作者: GOODWIN    時(shí)間: 2021-10-11 10:20

學(xué)習(xí)



歡迎光臨 Office中國(guó)論壇/Access中國(guó)論壇 (http://m.mzhfr.cn/) Powered by Discuz! X3.3