T-SQL 製造空資料 -- 應用RowNumber() 函式

這兩天有一個製作報表的需求，主要是為了在資料表中呈現出12資料，但問題是不管資料有沒有夠12筆，都需要補足這12個表格(Row)，如下圖

 這類的問題其實使用之前的RDLC來製作也不會太困難，因為在資料匯入RDLC之前，.NET的程式還可以做最後的控制，只需要判斷來源資料DataTable的Count是否有12筆，如果沒有再使用程式硬塞空資料進去也可以，但如果想要用SQL一步解決就需要費點腦筋了，要讓原本比較少的資料變多，其實只需要去join其他table即可達到，但與其join的table則筆數須至少大於6筆(因為這裡的需求)才可以，下面使用Northind DB做例子
由下圖可以知道Employees目前總共只有9筆資料

將Employees join Customers之後，資料就變為819筆之多了

到這裡為止把資料變多筆還算簡單，難度是在於如何只要12筆，在T-SQL中，想要12筆可以使用TOP 12來取得，但是在上面的例子中，因為join時並沒有給予任何join條件，所以資料是直接projection，並不能達到我們的要求，所以我們需要在join的時候給予join的條件，問題來了，今天只是為了要產生新資料而隨便去挑了一個table 來 join，但如果要給於join 條件，我們使用RowNumber() function，來幫這個Customers Table新增一個Num欄位來與原本的Employees.EmployeeID outer join，程式碼如下

WITH EmptyRowNum AS
(
 SELECT ROW_NUMBER() OVER( ORDER BY CustomerID) AS 'Num'
 FROM Customers
)
SELECT 
  ISNULL(LastName,'') LastName , 
  ISNULL(BirthDate,'') BirthDate
FROM
Employees
right outer join
(
 SELECT Num FROM EmptyRowNum WHERE Num <= 12
) TT
on EmployeeID = TT.Num 

結果如下

由於BirthDate欄位是DateTime型態，T-SQL使用ISNULL 函式會自動將期日期編為1900-01-01
，而使用CTE是因為T-SQL在使用RowNumber() 函式的時候，並無法直接對其 alias name做條件判斷，這個問題在PL\SQL中並不會發生，有興趣的人可以看一下PL\SQL中的rownum功能，而這裡Customers Table就會提供給我們num欄位所限定的條件筆數。下一篇文章將會使用這個結果來製作RDLC報表。