[SQL] 將關聯資料彙整成單一欄位並傳回的小技巧

今天在設計 Master-Detail 顯示畫面時, 面對煩雜的關聯資料, 一時突發奇想, 然後隨口問了公司的 DBA 老大, 想請教他有沒有什麼想法, 結果竟然有解法, 而且異常的簡單! 所以就有了這一篇文章的出現。(順便說一下, 對一個沒有時間去鑽研資料庫的開發人員而言, 公司裡有位 DBA 是件蠻幸福的事情)

我的需求是這樣的。我把客戶和電話分別存成兩個資料表, 分別是 tblCustomer 和 tblPhone, 兩者之間以 CustomerId 對應。一個客戶可以不只有一支電話, 可能有很多支電話; 使用兩個表來關聯, 就可以形成一對多的關係, 類似 Linked List。

一般而言, 假設我的客戶還關聯到其它資料表, 例如 tblOrder, 那麼我可能會做兩層的 Master-Detail, 也就是以 cascading (或者說「瀑布」) 方式呈現。但是如果它有更多的關聯呢? 例如這個 tblCustomer 又關聯到 tblAddress, 還有 tblEmail, tblChildren, tblBankAccount…, 那麼這個呈現方式不但像瀑布, 它還分流了! 如此一來, 畫面就會變得相當雜亂無章, 很難規畫。

面對這類問題, 我以前都會使用翻頁的方式處理, 也就是 Master-Detail 都只做一層, 更複雜的關聯都以開新頁的方式處理。但是這麼做的話, 簡捷是簡捷了, 卻總被客戶嫌不夠方便、無法馬上一覽全局。

有些朋友則很愛玩大腸包小腸的遊戲, 也就是層層疊疊地在 GridView 裡包 GridView 或者 DetailedView 之類的 container。但是我從來不認為那是好方法, 因為我認為這種處理方式恰巧是所有雜亂無章的畫面中最雜亂無章的那一種, 更別說開發者還要應付不容易處理的網頁生命流程了。

經過 DBA 老大的提點之後, 我學會使用 SQL 的 COALESCE 指令來處理這類需求。

問題的做法非常簡單, 但是這個 COALESCE 指令, 我覺得我沒辦法把它解釋得很好, 讀者們恐怕必須自己花點時間把它搞清楚, 或者不用把它搞清楚, 直接拿去照用吧!

其實 Coalesce 這是一個英文字; 雖然它的拼法很奇怪, 但是並不是什麼縮寫, 跟煤也沒什麼關係。它是「聯合」、「接合」的意思; 重音在第二音節。

來看程式碼:

SELECT @Phones = COALESCE(@Phones + ', ', '') + tblPhone.PhoneNumber 
FROM [tblCustomer] INNER JOIN 
 [tblPhone] on tblCustomer.CustomerId = tblPhone.CustomerId
WHERE tblCustomer.CustomerId = @CustomerId

以上這段查詢式是寫被我寫在 Stored Procedure 的。@Phones 是我定義的 nvarchar 變數, @CustomerId 則是一個輸入參數。

如果你把程式中的 "@Phones = COALESCE(@Phones + ', ', '') + " 拿掉, 這個 SELECT 就會變成一段很平常的 SELECT 指令, 而且它可能會傳回如下的結果集:

• (02)1234-5678
• 0912-345-678
• 0800-123-456

但是加上了 COALESCE 指令之後, 它變成 "(02)1234-5678, 0912-345-678, 0800-123-456" 這樣的一個簡單的字串。然後, 把它串到最上層的查詢結果裡:

SELECT tblOrder.OrderId, ..., tblCustomer.Name, ..., @Phones AS Phones   
FROM tblOrder INNER JOIN tblCustomer ON tblOrder.CustomerId = tblCustomer.CustomerId
WHERE tblOrder.OrderId = @OrderId

上述這個查詢指令只會傳回一個 datarow, 而不是塞滿了重複資料的結果集。如此, 你的查詢畫面也不需要做 Master-Detail 了! 這樣是不是精簡多了呢?

歡迎加入 .NET 2Share on FaceBook 歡迎加入 .NET 2Share on Google+