那些年，我們一起背的經書

2012年6月16日，竹區兒童讀經班在新竹縣新豐鄉的山崎國小禮堂合辦了一場讀經班結業式。這些日子裡，每一個參與的父母、小朋友、老師、義工，都注入了一分心業力，因為有你們，我們才有所成長，也因為有你們，我們才得以感動。

上面是當天所播放的回顧影片，正如影片最後所說：

我們是何等的光榮

可以站在

改變人生的起跑點上

又是如此地歡喜

能與一群無私付出的師生

攜手並肩而行

各區讀經班教室網址：http://educational.blisswisdom.org/content/view/279/280/
2012年5月5日新竹支苑母親節奉茶站照片

preventDefault 與 returnValue

記得剛開始認識 「event.preventDefault();」 的時候，感覺在程式裡面有機會把它找幾個位置擺放，是一件很幸福的事情，這種感覺就好像能在家裡擺個花瓶是一樣的（沒多大用處，就好看而已）。沒多久，我又認識了「event.returnValue=false;」，雖然不缺，但內心深處還是有個寂寞的影子，於是我踰越了道德的那條線。表面上看似春風如意，直到有一天，我發現 IE容不下「event.preventDefault();」，FireFox 與「event.returnValue=false;」互為水火，讓我開始重新思考，誰才是我最後的選擇。後來我發現，探究到最核心，其實，只有回到最初錯誤的開始：「return false;」，才能真正見容於 IE 與 FireFox ！

多年後的今天，我終於領悟，從頭到尾，愛我與接受我的，只有 Chrome 與 Opera，這些日子他們一直都在，一直都在我身邊不離不棄，只能說：人間自有真情在啊！！

語法/瀏灠器支援情況	IE	FireFox	Chrome	Opera
event.preventDefault();	X	Ⅴ	Ⅴ	Ⅴ
event.returnValue=false;	Ⅴ	X	Ⅴ	Ⅴ
return false;	Ⅴ	Ⅴ	Ⅴ	Ⅴ

瀏灠器測試版本：
IE:8.0.7601.17514
FireFox:13.0
Chrome:19.0.1084.56
Opera:11.62

Ref:
01:解决firefox不支持window.event.returnValue = false
02:JavaScript 中的attachEvent与addEventListener方法
03:createEvent-dispatchEvent and preventDefault example

CTE Recursive

於 MSDN 「Recursive Queries Using Common Table Expressions」所說，遞迴的 CTE 必須包含兩個部份，一個是固定部份，另一個則是遞迴部份。而整個遞迴 CTE 的架構長的如下所示：

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- 固定部份的定義.
UNION ALL
CTE_query_definition –- 遞迴部份的定義，且參考了 cte_name
)
-- 使用 CTE 的宣告
SELECT * FROM cte_name

舉一個範例來說明，假設某家公司從 BOSS 開始，下面分了 AP 與 CT 兩個事業處，而每個事業處底下又分了好幾個部門。

create table #tmp(CompanyID int not null primary key,
ParentCompanyID int null,
CompanyName nvarchar(50) )

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(1,NULL,'BOSS')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(2,1,'AP')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(3,1,'CT')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(4,2,'E100')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(5,2,'E200')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(6,3,'K100')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(7,3,'K200')

insert into #tmp (CompanyID,ParentCompanyID,CompanyName)
values
(8,3,'K300')

select * from #tmp

有了以上的基本資料後，接下來透過 CTE 的遞迴語法來獲得公司的樹狀結果。

;with CompanyTree(ParentCompanyID,CompanyID,CompanyName,CompanyLevel)
as
(
 select ParentCompanyID,CompanyID,CompanyName,0 as CompanyLevel from #tmp 
 where ParentCompanyID is null
 union all
 select c.ParentCompanyID,c.CompanyID,c.CompanyName,p.CompanyLevel+1 from #tmp c
 inner join CompanyTree p on c.ParentCompanyID=p.CompanyID
)
select * from CompanyTree OPTION (Maxrecursion 20)

drop table #tmp

上面 CTE 的語法中，固定部份是：

select ParentCompanyID,CompanyID,CompanyName,0 as CompanyLevel from #tmp
where ParentCompanyID is null

這語法很明顯是把父子關係裡我們所謂的根節點 Root 找出來，所以他的查詢條件是找父親索引 (ParentCompanyID) 為 NULL 的資料。自訂欄位 CompanyLevel 則是用來顯示樹狀結構的深度，由於在固定部份所取得的都是 Root 資料，所以樹狀深度都是 0，所得到的結果用 T₀ 來表示 .

接下來，則是透過 UNION ALL 將遞迴部份的資料加進來。

select c.ParentCompanyID,c.CompanyID,c.CompanyName,p.CompanyLevel+1 from #tmp c
inner join CompanyTree p on c.ParentCompanyID=p.CompanyID

我們用 T₁ 來表示第 1 次迴圈的結果集合。由目前的範例，我們會獲得

主要是因為在固定部份 T₀ 我們一開始得到的是

所以透過 c.ParentCompanyID=p.CompanyID
(或視為 #tmp.ParentCompanyID = CompanyTree.CompanyID)
的關聯條件就可以得到 AP 與 CT 兩筆資料。這一次的關聯條件，翻譯成白話，就好像是問說：原始資料裡面，有誰的爸爸叫做 1 的阿？而翻譯成SQL 條件，就是：

#tmp.ParentCompanyID=1

，於是就會跑出 AP 與 CT 這兩個結果。

接著進行第 2 次迴圈後，則 T2 所得到的結果，分別是 ：

AP 的

與 BP的

這兩部份的加總，也就是 E100、E200、K100、K200。


而遞迴的特性，會一直持續的往下探尋，直到找不到資料為止，也就是從 T₁ … T_n 。
目前的範例，則是將  T₁,T₂ 這 2 層的資料回傳。
最後的樹狀結果加總 T₀ … T_n 所有資料 ，如下所示：

或許一開始會有個疑問，為什麼一定要用 UNION ALL ，為什麼一定要用 inner join，但當你試著用其他選擇，例如改成 UNION 後，會得到以下錯誤訊息：

Recursive common table expression 'CompanyTree' does not contain
a top-level UNION ALL operator.

改成 left join 後會得到

Outer join is not allowed in the recursive part of a
recursive common table expression 'CompanyTree'.

就可以發現，其他的選擇，會造成 CTE 遞迴的語法錯誤，沒得商量。

此外，CTE 其實還有一個比較特別的參數設定可以使用。由於其強大的遞迴功能，為了避免使用者因為邏輯上的錯誤而造成了無限迴圈，他提供使用者自行定義要往下探尋幾層的限制。假設你已經知道自己的樹狀結構不會超過 10 層，那你可以在程式裡面加上  OPTION (Maxrecursion 10)，當迴圈超過10層時，程式就會出現錯誤訊息而終止，不會跑無限迴圈。
超過10 層的錯誤訊息：

The statement terminated. The maximum recursion 10 has been exhausted
before statement completion.

完整程式如下：

;with CompanyTree(ParentCompanyID,CompanyID,CompanyName,CompanyLevel)
as
(
 select ParentCompanyID,CompanyID,CompanyName,0 as CompanyLevel from #tmp 
 where ParentCompanyID is null
 union all
 select c.ParentCompanyID,c.CompanyID,c.CompanyName,p.CompanyLevel+1 from #tmp c
 inner join CompanyTree p on c.ParentCompanyID=p.CompanyID
)
select * from CompanyTree OPTION (Maxrecursion 10)

這個 Maxrecursion 或許你不會想去設定，但必須要注意，即便你沒設定，但它的預設值卻幫你設定好了，預設是 100 ，也就是說，當你的探尋深度有可能超過100 時，記得要將這個參數調整為設合您的大小。他的設定範圍為 0 ~ 32767，0 則表示沒有限制，也就是說可以超過 32767，但請注意，當你的迴圈深度達到這麼深時，整個的執行效能是非常差的。


Ref:
01:Recursive Queries Using Common Table Expressions

02:一般資料表運算式(Common Table Expressions, CTE)

關於 SQL 的 Common Table Expression

Common Table Expression (CTE) 是 SQL Server 2005 開始加入的新成員，千萬別跟 ETC 搞混囉（雖然把名字倒過來唸還真的一模一樣）！！

會對 CTE 開始有興趣，是因為以前在撰寫比較複雜且多層的子查詢時，自己當下是很清楚要寫什麼，可是經過一段時間後，自己已經有點不懂當時的想法，更不用說其他接手維護的工程師了。舉例來說，以前寫的好幾層子查詢，可能長得如下：

select A1 from A where A2 in
 (
        select B1 from B where B2 in
                    ( select C1 from C where C2 like '%test%')
 )

透過 CTE，可以讓我們不用這麼辛苦，可以暫時先把每個子查詢的結果存放在一個記憶體變數中，而結果會如下：

;with tC as
      (select C1 from C where C2 like '%test%')
,tB as
      ( select B from B where B2 in (select * from tC))
select A1 from A where A2 in (select * from tB)

在 Insert 的用法：

create table #tmp (Country nvarchar(200),OkNum int)

;with T as
(select Country,OkNum from AsiaData )
insert into #tmp select Country,OkNum from T

select * from #tmp
drop table #tmp

在 Group 的用法：

;with T as
(
   select Country,OkNum from AsiaData union select Country,OkNum from EuropeData
)
select Country,sum(isnull(OkNum,0)) OkNum from T
group by Country

個人覺得，使用 CTE，程式碼不見得會寫得比較少，但至少對閱讀上來說，的確是比較容易理解。在使用上，除了一般所撰寫的 T-SQL 查詢之外，還可將 CTE 用於使用者自訂的函數( Function)、預存程序( Stored Procedure)、檢視( View )、觸發程序( Triggers )。

CTE 語法的基本架構如下(Ref 01:Using Common Table Expressions)：

--定義 CTE
;WITH CTE_運算式名稱 [ ( column_name [,...n] ) ]
AS
( CTE_查詢_定義 )

--使用 CTE
SELECT <column_list> FROM CTE_運算式名稱;

在定義 CTE 時，如果沒有指定欄位名稱，則結果的欄位名稱預設會跟 CTE_查詢_定義 裡的結果一樣；如果有指定欄位名稱，則欄位名稱的值會跟 CTE_查詢_定義 裡出現的結果順序一樣，且 CTE 的欄位名稱是不可重複的。舉例來說：

;with T (T1,T2) as
     (select C2,C1 from C )

這裡要注意，CTE 的結果裡，T1 欄位所呈現的值會是 資料表 C 的 C2 欄位，原因是 CTE 的結果欄位是依據 CTE_查詢_定義 的順序來決定。(Ref 02:SQL with)

使用 CTE ，有個比較特別的現象，他並不像 View 或 暫存資料表一樣可以一直被引用，實際上，他只能被引用一次。也就是說，當你定義完 CTE 的結構後，你必須在後面接著馬上使用他，使用完了之後，他就不見了。

;with EuropeData (Country,OkNum) as
(
   select top 5 Country,OkNum from AsiaData

)
-- 第一次查詢
select top 5 Country,OkNum from EuropeData
-- 第二次查詢
select top 5 Country,OkNum from EuropeData

在上面的範例中，一開始我定義了 CTE 的運算名稱為 EuropeData ，其實這名稱也剛好是我資料庫裡的某一個資料表，只是我刻意把 CTE 的名稱取的跟我既有的資料表名稱一致，只是 CTE 的結果，其實是從 AsiaData 而來。當定義完我的 CTE 之後，連續 2 次執行查詢，則可以在結果中發現，第一次的查詢，資料是從 AsiaData 而來。但第二次的查詢，因為自訂 CTE 的記憶體空間已被回收，所以會抓到原先資料庫已經存在的 EuropeData 資料表。

CTE 除了上述的介紹之外，他還有另一個蠻有趣的主題：遞迴，這功能可以讓我們在處理行父子關係的資料表、組織圖，更有效率，於後面再詳述。

Ref：

01.Using Common Table Expressions
02.SQL with
03.SQL中使用WITH AS提高性能－使用公用表表达式（CTE）简化嵌套SQL
04.一般資料表運算式(Common Table Expressions, CTE)
05.T-SQL -- COMMON TABLE EXPRESSION (CTE) 教學重點筆記
06.SQL 2005 T-SQL Enhancement: Common Table Expression
07.Performance Effect of Common Table Expressions in SQL Server 2005