根據上一行填充本行的空白欄位，SQL處理方式

深藍發表於2015-01-23
SQL

我在4年多前，寫了一篇Excel處理空白Cell的文章，http://www.cnblogs.com/studyzy/archive/2010/04/07/1706203.html，其實在資料庫中也會遇到這種情況。對於普通的OLTP系統來說，應該不會出現，主要是在做OLAP，匯入外部資料來源時，可能匯入系統的就是帶有空白記錄的資料。

為了方便說明，我舉了一個簡單的例子，假設一個學生成績表，有欄位“學生ID”和“成績”，學生ID是主鍵，自增，成績只有NULL和1，2，3，4，5這幾個值。在錄入學生成績的時候，如果成績為NULL，就表示該學生成績和上一個學生的成績相同。現在要查詢某個學生ID的成績，該怎麼查呢？或者要將成績欄位改為不允許為空，怎麼把所有NULL的行填上成績呢？

首先我們先建立示例表：

 1 create table t1
 2 (
 3 ID int identity primary key,
 4 Score int null
 5 );
 6 insert t1
 7 values(3),(4),(null),(3),(null),(null),(5);
 8 
 9 select *
10 from t1


NewImage

 

 

 

 

 

 從結果我們可以看到如果要查詢學生6的成績，那麼應該先去查學生5的成績，由於學生5也是空，所以要繼續查前一個學生4的成績，得到分數3，所以學生6的成績是3.這顯然是一個遞迴問題，如果一直是空，會繼續遞迴下去，直到找到一個成績為止。要在SQL中使用遞迴，那麼第一個應該想到的就是公用表表示式CTE。關於CTE的語法和說明可以看MSDN：https://msdn.microsoft.com/zh-cn/library/ms186243.aspx

那麼我們這裡遞迴的終點是什麼呢？是不為空的成績，遞迴的連結條件是上一個學生ID=當前學生ID-1.於是我們可以將此次的公用表表示式寫為：

 1 with t
 2 as
 3 (
 4 select * from t1 where Score is not null
 5 union all
 6 select t1.ID,t.Score
 7 from t
 8 inner join t1
 9 on t.ID+1=t1.ID
10 where t1.Score is null
11 )
12 select *
13 from t
14 order by ID;

得到的結果為：

NewImage

這裡的情況比較特殊ID是連續的，那麼如果ID不連續會怎麼樣呢？我們試著刪除ID=5

delete from t1 where ID=5

這個時候如果還是執行上面的CTE就會查不到ID=6的記錄，因為inner join的條件不成立了。那麼簡單的辦法就是使用開窗函式給每一行資料增加一列連續自增的列，SQL Server中的函式是ROW_NUMBER().這樣就變成了兩個CTE巢狀使用，請看程式碼：
 1 with t1new
 2 as
 3 (
 4 select *,ROW_NUMBER() over(order by ID) as RowNo
 5 from t1
 6 )
 7 , t
 8 as
 9 (
10 select Id,Score,RowNo from t1new where Score is not null
11 union all
12 select t1new.ID,t.Score,t1new.RowNo
13 from t
14 inner join t1new
15 on t.RowNo+1=t1new.RowNo
16 where t1new.Score is null
17 )
18 
19 select *
20 from t
21 order by ID
NewImage

公用表表示式真的很強大，另外在使用View出Report的時候，也可以用CTE，因為在View中不能用臨時表，所以使用CTE代替臨時表是個不錯的解決方案。

相關文章