淺談數(shù)據(jù)庫開發(fā)（五）——從寬表到存儲過程（之四）

roych · 發(fā)表于 2019-11-16 16:00:38

我們在前面已經(jīng)寫了一個存儲過程，但很明顯，我們的update語句挺多的，9個字段，就需要寫個update語句，假若某天老板要看一個月的，豈不是要定義31個變量，然后更新31次？我們也沒有更好的辦法呢？答案是：有。
——寫循環(huán)語句。

我們知道，這些update語句的格式都是相似的。理論上，我們可以把update那部分剝離出來，作為一個子過程來調(diào)用，寫循環(huán)即可。但現(xiàn)實是，更新的字段不一致，這將涉及到SQL語句字符串的拼接。所以暫時我們不考慮用子過程的方式來完成這個任務，而是就地修改存儲過程。
對于經(jīng)驗不太豐富的新手，在拼接SQL字符串時，個人建議先使用print的方法來檢查語法是否正確。正確后，再進行正式的執(zhí)行。
我們先來看看這些語句是結(jié)構(gòu)：

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可以作為一個循環(huán)變量（day0~day8），而b表中的@day0同樣也可以作為一個循環(huán)變量（@day0~@day8），而其它的則無需更改。此外，day0和@day0原則上應該是一一對應的。因此，接下來，我們可以聲明一個SQL字符串用于存放語句，聲明一個@i用于循環(huán)賦值，并且初始化，右擊，修改存儲過程：

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: 用于完成用戶留存率數(shù)據(jù)源的計算
-- =============================================
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
*/
--如果使用默認值（即無輸入），則預設為系統(tǒng)日期前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
/*
--清空舊數(shù)據(jù)
truncate table log_result
--插入新數(shù)據(jù)（這里只插入day0~day8的數(shù)據(jù)，并初始化登錄天數(shù)為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的具體長度，可以根據(jù)實際設置。我這里稍稍設置偏大一些。由于我們的目的是檢查SQL語句，因此，可以先把前面的各種操作注釋掉（truncate，insert和update等）。完成后點工具欄上的三角按鈕“執(zhí)行”。然后同樣按前面提到的方法執(zhí)行，結(jié)果生成了這樣的語句：

我們留意到，這個并不是日期格式（日期格式是需要加上單引號的）。因此需要修改前面的語句為：

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語句中的用法。使用單引號時，是加一對的，請自行對比。這時候同樣再執(zhí)行一次，得到正確的結(jié)果。這里就不再截圖了。
可能有人會問，如果本身就是字符串，還里面還有單引號怎么辦？答案很簡單，再加一對�！@種情況也是有的。在鏈接服務器上同步數(shù)據(jù)時，其中一個參數(shù)就是SQL字符串，以單引號括起來引用。如果需要傳日期值給這個字符串，那么就得看加上兩對（即4個單引號）了。詳情請留意拙作：
巧用鏈接服務器同步數(shù)據(jù)
這里暫時不討論這個問題。確實不懂的版友回復本帖后，我再貼答案。

至此，我們寫了一個簡單的SQL語句拼接，接下來，我們將它修改為while循環(huán)，當然這時候的@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

復制代碼

同樣地，我們執(zhí)行修改后，再來執(zhí)行一下存儲過程，于是得到以下結(jié)果：
看起來，確實是我們想要的結(jié)果。
既然語句沒問題了，那么，我們就可以把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: 用于完成用戶留存率數(shù)據(jù)源的計算
-- =============================================
ALTER PROCEDURE [dbo].[sp_user_count] @min_date date=null
AS
BEGIN
SET NOCOUNT ON;
--定義9個日期變量
declare @SQL varchar(500)
declare @i int
--如果使用默認值（即無輸入），則預設為系統(tǒng)日期前8天。
if @min_date is null
set @min_date=dateadd(day,-8,GETDATE())
--初始化
set @SQL=''
set @i=0
--清空舊數(shù)據(jù)
truncate table log_result
--插入新數(shù)據(jù)（這里只插入day0~day8的數(shù)據(jù)，并初始化登錄天數(shù)為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
--執(zhí)行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

復制代碼

這樣，看起來是不是清爽多了？驗證一下，看看結(jié)果如何：

看起來沒什么問題。那么結(jié)果表就可以用這樣寫了：

稍稍對比下，看上去應該對得上。

至此，一個存儲過程就開發(fā)完成了。學會了么？附上附件，供參考。

有什么問題，歡迎跟帖。

GOODWIN · 發(fā)表于 2021-10-11 10:20:35

學習

帳號		自動登錄	找回密碼
密碼			注冊

主題相關(guān)群

地區(qū)相關(guān)群[您還沒設置居住地]

淺談數(shù)據(jù)庫開發(fā)（五）——從寬表到存儲過程（之四）

本帖子中包含更多資源

群介紹
主題無相 (隨機顯示版	同地區(qū)群塊其他地區(qū))

群介紹
Access免費培訓群2 (792054000)