使用 MS SQL CTE (Common Table Expression) 的遞迴功能

MS Sql Server 有提供 CTE (Common Table Expression)，它可以讓我們用 WITH 語法來建立暫時的 Table。

例如：

WITH numRange(num) AS (
	SELECT 1
)
SELECT *
FROM numRange

查詢結果是：

1

而因為 CTE 可以在 AS () 中參考自已本身，
所以可以達到遞迴的效果，今天就是要來紀錄一下使用的方法。

在 AS () 中，會由兩種成員組成，分別是放在前面的錨點成員 (anchor) 和放在後面的遞迴成員 (recursive)，錨點成員是用來設定初始的 Table 內容，不可參考到 CTE 本身的 Table。

而遞迴成員會參考到 CTE Table 本身 (準確來說是參考到上一次迭代得到的資料表)。

錨點成員跟遞迴成員必須要用 UNION ALL 連接。

在每一次的遞迴迭代中，遞迴成員會參考到上一次得到的資料表，而做完 SELECT 後會產生出一份屬於這次迭代的資料表給下一次迭代的遞迴成員使用，迭代會一直進行下去直到超過次數上限 (可以用 OPTION (MAXRECURSION X) 來設定上限，X是次數，設 0 代表無限) 或是產生不出資料表為止 (就是 SELECT 的結果為空集合)。

當所有迭代執行結束後，它會把所有產生出的資料表結合起來得到最後的結果資料表。

下面舉個例子：

WITH numRange(num) AS (
	SELECT 1

	UNION ALL

	SELECT num + 1
	FROM numRange
	WHERE (num + 1) <= 3
)
SELECT *
FROM numRange

查詢結果是：

1

2

3

一開始初始第1次迭代的資料表是：

1

第2次迭代產生的資料表是

2

第3次迭代產生的資料表是

3

而第4次因為 SQL 語句 (此時 num 參考上一次、也就是第三次迭代的 num 是 3)

SELECT num + 1
	FROM numRange
	WHERE (num + 1) <= 3

得到的結果集為空，所以迭代結束。

接著下面來看另一個範例，
我們有一張表叫做 family，欄位是 name, father, mother，分別代表家族成員的名字、父親名字、母親名字，假設名字都不會重覆，所以名字可以當做 id 主鍵來做 JOIN。

表的內容如下：

name	mother	father
小孩1	媽媽	爸爸
小孩2	媽媽	爸爸
媽媽1	外婆	外公
爸爸	祖父	祖母
祖父	NULL	NULL
祖母	NULL	NULL
外公	NULL	NULL
外婆	NULL	NULL

現在我們想找出特定人的所有長輩及其的父母、還有長輩和特定人的輩份距離，就可以像下面這樣查詢，範例是要找出"小孩1"的長輩及其父母和長輩跟"小孩1"的輩份關係，相關的說明也都已寫在範例的注釋中：

-- 建立資料表
CREATE TABLE family(
   name NVARCHAR(100),
   mother NVARCHAR(100),
   father NVARCHAR(100)
);

-- 塞資料
INSERT INTO family(name, mother, father)
VALUES(N'小孩1', N'媽媽', N'爸爸'),
      (N'小孩2', N'媽媽', N'爸爸'),
	  (N'媽媽', N'外婆', N'外公'),
	  (N'爸爸', N'祖父', N'祖母'),
	  (N'祖父', NULL, NULL),
	  (N'祖母', NULL, NULL),
	  (N'外公', NULL, NULL),
	  (N'外婆', NULL, NULL);

-- 進行查詢
WITH elderRelation(elder, elder_mother, elder_father, level) AS (
   -- 這裡是錨點成員，建立一開始的初始資料集
   -- 例如此例是找出 "小孩1" 的父母及其父母和小孩與父母的輩份相差數

   -- 只要沒有參考到 elderRelation CTE 本身就是屬於錨點成員
   ---------- 錨點成員 - 開始 ----------

   -- 查詢"小孩1"的母親及其父母資料
   SELECT parent.name, parent.mother, parent.father, 1
   FROM family person INNER JOIN family parent ON person.mother = parent.name
   WHERE person.name = N'小孩1'

   UNION ALL

   -- 查詢"小孩1"的父親及其父母資料
   SELECT parent.name, parent.mother, parent.father, 1
   FROM family person INNER JOIN family parent ON person.father = parent.name
   WHERE person.name = N'小孩1'

   -- 上述只是展示錨點成員也可以自己視需要用 UNION 組合多個 Table，
   -- 上述範例也可以用一次查詢完成，例如：
   --SELECT parent.name, parent.mother, parent.father, 1
   --FROM family person INNER JOIN family parent ON person.mother = parent.name OR person.father = parent.name
   --WHERE person.name = N'小孩1'

   ---------- 錨點成員 - 結束 ----------

   -- 用 UNION ALL 與遞迴成員組合
   UNION ALL

   -- 下面因為開始參考到 elderRelation 這個 CTE 本身，所以是遞迴成員
   ---------- 遞迴成員 - 開始 ----------
   -- 找出 elderRelation 母親的父母人員資料 
   SELECT family.name, family.mother,  family.father, level + 1
   FROM elderRelation INNER JOIN family ON elderRelation.elder_mother = family.name

   UNION ALL

   -- 找出 elderRelation 父親的父母人員資料
   SELECT family.name, family.mother,  family.father, level + 1
   FROM elderRelation INNER JOIN family ON elderRelation.elder_father = family.name

   -- 上述只是展示遞迴成員也可以自己視需要用 UNION 組合多個 Table，
   -- 上述範例也可以用一次查詢完成，例如：
   --
   --SELECT family.name, family.mother,  family.father, level + 1
   --FROM elderRelation INNER JOIN family ON elderRelation.elder_mother = family.name OR elderRelation.elder_father = family.name

   ---------- 遞迴成員 - 結束 ----------
)
SELECT *
FROM elderRelation
OPTION (MAXRECURSION 0); -- 可以視需要設置遞迴的最大允許次數，0代表無限大

查詢結果如下：

elder	elder_mother	elder_father	level
媽媽	外婆	外公	1
爸爸	祖父	祖母	1
祖父	NULL	NULL	2
祖母	NULL	NULL	2
外婆	NULL	NULL	2
外公	NULL	NULL	2

參考資料：

1. WITH common_table_expression (Transact-SQL)
2. 利用 MAXRECURSION來突破CTE預設遞迴次數
3. [SQL] 使用 CTE 遞迴查詢 (PostgreSQL / MSSQL)
4. [SQL Server] CTE RECURSIVE (遞迴)製作月曆

Jaspersoft Report 要如何將 Java List 送進 Report 當 Datasource 和 Parameter

在使用 Jaspersoft Report 時，除了直接在 jrxml 檔中設定 SQL 語法去 Database 查資料以外，
也可以在 Java 程式中先去 Database 中查資料，查好後再把資料送去給 Jaspersoft 產生報表。

在每個子報表 (Sub Report) 的資料來源需求都是同一個查詢，頂多 SQL Where 條件不一樣時，
這種方式可以只要用 Java 端查詢一次資料送給各子報表，
各個子報表再使用 Filter 功能去實現自己的 Where 條件篩選，
就可以避免每個子報表都要進行一次 SQL 查詢而影響效能。

以下紀錄一下使用範例。

在 Java 中：

//自己想辦法查出資料成一個 List 物件
List dataset = getDataset();

//把 List 放進 reportParams Map 中
Map reportParams = new HashMap();
reportParams.put("dataset", dataset);

//把 reportParams Map 當參數送進 Jaspersoft Report 中
//然後子報表 (Sub Report) 可以把 Dataset 包裝成 Datasource 使用
JasperReport jasperReport = JasperCompileManager.compileReport("myReport.jrxml"));
//Dataset 也可以作為 Data source 送給主報表 (Main Report）使用
JRDataSource dataSource = new JRMapCollectionDataSource(dataset);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, reportParams, dataSource);

在 Main Report 的 jrxml 檔中：

<!--設定名為 dataset 的 parameter 跟 Java 傳進來的 dataset 參數作對應  -->
<parameter name="dataset" class="java.util.Collection"/>

<!-- 用 JRMapCollectionDataSource 把 $P{dataset} 包裝成一個新的 DataSource 傳給 Sub Report  -->
<!-- 不把 Main Report 的 DataSource 直接傳給 Sub Report 是因為 DataSource 被讀取 (Consume) 後就不能再讀了， -->
<!-- 會造成 Sub Report 把 Main Report 的 DataSource 吃掉讓 Main Report 讀不到資料。  -->
<subreport>

...........

 <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRMapCollectionDataSource($P{dataset})]]></dataSourceExpression>

............

</subreport>

在 Sub Report 的 jrxml 檔中：

<!-- 在 Sub Report 中可以用 <filterExpression> 的功能過濾篩選資料，實現像 SQL Where 的效果 -->
<!-- 例如下面範例： -->
<!-- 從 Main Report 得到的 DataSource 取得的 Dataset 中， -->
<!-- 有名為 groupName 的 String 欄位 (field)， 設定 field 去接收它，例如值可能有 1, 2, 3, 或 4 -->
<field name="groupName" class="java.lang.String"/>
<!-- 設定 parameter 去接收 Main Report 傳給 Sub Report 的另一個名為 groupNameForFilter 的參數，例如值為 3 代表想篩選出只是 3 的 DataSet -->
<parameter name="groupNameForFilter" class="java.lang.String"/>
<!-- 用 filterExpression 功能去設定篩選條件為：$F{groupName} 要等於 $P{groupNameForFilter}  -->
<filterExpression><![CDATA[$F{groupName}.equals($P{groupNameForFilter})]]></filterExpression>