在撈報表資料時，有時會遇到一對多的資料，比方說一張訂單裡有多個商品，更往往會被要求這些多筆的資料，放進一個比方說購買商品的欄位裡作呈現。但其實，這對以 RowData 為概念的資料庫操作而言，並不是那麼理所當然。速記一下這一篇…免得每次遇到這種 Case 總要搞半天…
就舉訂單編號與訂單商品間一對多的資料關係作為例子：
原始資料 (Base Data): ORDER_NO PROD_NAME ---------- ---------- ODR0001 PROD_A ODR0001 PROD_B ODR0001 PROD_C ODR0002 PROD_D ODR0002 PROD_E ODR0003 PROD_F ODR0003 PROD_G ODR0003 PROD_H ODR0003 PROD_I 期望輸出 (Desired Output): ORDER_NO PROD_LIST ---------- -------------------------------------------------- ODR0001 PROD_A,PROD_B,PROD_C ODR0002 PROD_D,PROD_E ODR0003 PROD_F,PROD_G,PROD_H,PROD_I
在 Oracle 11g Release 2 之後，有個方便的內建 function LISTAGG 可以使用：
SELECT order_no, LISTAGG(prod_name, ',') WITHIN GROUP (ORDER BY prod_name) AS prod_list FROM orders GROUP BY order_no; == Result =================================================== ORDER_NO PROD_LIST ---------- -------------------------------------------------- ODR0001 PROD_A,PROD_B,PROD_C ODR0002 PROD_D,PROD_E ODR0003 PROD_F,PROD_G,PROD_H,PROD_I 3 rows selected.這種解法十分簡潔，從 SQL 語法上也還不難理解，但世事往往不能盡如人意，如果像阿布一樣，遇上的資料庫環境是更早的 Oracle 版本，那這條捷徑是行不通的。
原則上，阿布還是希望在不 CREATE 額外的 PROCEDURE / FUNCTION，在不借助額外 COLLECT / OBJECT 的前提下，單純以 SQL SELECT 來達成。因為客觀來講，往往在使用 Oracle 這種規模的資料庫環境，有時要下個 CREATE 是要跑過層層的簽核程序的，如果是尋求 Long-term Solution 是無可厚非，但是往往面臨的是臨時的需求或是一次性使用，殺雞焉用牛刀，也緩不濟急。
在 williamrobertson.net 搜尋到了讓阿布眼睛一亮的 solution。在 Oracle 9i 之後，可以使用 ROW_NUMBER() 和 SYS_CONNECT_BY_PATH 函數來實作相同的結果：
SELECT order_no,
LTRIM(MAX(SYS_CONNECT_BY_PATH(prod_name,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS prod_list
FROM (SELECT order_no,
prod_name,
ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY prod_name) AS curr,
ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY prod_name) -1 AS prev
FROM orders)
GROUP BY order_no
CONNECT BY prev = PRIOR curr AND order_no = PRIOR order_no
START WITH curr = 1;
== Result ===================================================
ORDER_NO PROD_LIST
---------- --------------------------------------------------
ODR0001 PROD_A,PROD_B,PROD_C
ODR0002 PROD_D,PROD_E
ODR0003 PROD_F,PROD_G,PROD_H,PROD_I
3 rows selected.節錄 williamrobertson.net 的說明：It's possible to use the analytic ROW_NUMBER() function to simulate a hierarchy in the ordered data, and then in an outer query use SYS_CONNECT_BY_PATH (new in 9i) to show that "hierarchy" as one line, and take the last value in each group using the handy KEEP (DENSE_RANK LAST) construction also added in 9i.概念上，就是用 ROW_NUMBER() 去做出階層式的有序數據，然後用 SYS_CONNECT_BY_PATH 把階層式的有序數據連結，然後用 KEEP (DENSE_RANK LAST) 取回各群組中最後的值作為結果，而 function SYS_CONNECT_BY_PATH 與 KEEP (DENSE_RANK LAST) 架構都是 Oracle 9i 新增的。老實講，阿布功力不夠看得很模糊…雖然這或許不是個有效率的，也不是個優雅的 Query，但是這個 solution 無需使用 PL/SQL 或額外的附加類型定義，完美！
參考資料︰
再來，快速帶過一個三個 Table 關連的範例。
情境︰有個 Table employee 放了員工的基本資料，一個 Table role 放著角色(權限群組)的資料，再來一個 Table employee_role_mapping 對應出各員工的角色，而一個員工可能被賦予多個角色，在撈帳號清單時，需要把這個員工的角色列在同一個欄位中。
Oracle 9i 版:
SELECT employee_no,
LTRIM(MAX(SYS_CONNECT_BY_PATH(role_name,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS role_list
FROM (SELECT p.employee_no,
r.role_name,
ROW_NUMBER() OVER (PARTITION BY p.employee_no ORDER BY r.role_name) AS curr,
ROW_NUMBER() OVER (PARTITION BY p.employee_no ORDER BY r.role_name) -1 AS prev
FROM employee p, employee_role_mapping m, role r
WHERE p.employee_no= m.employee_no
AND m.role_id = r.role_id)
GROUP BY employee_no
CONNECT BY prev = PRIOR curr AND employee_no = PRIOR employee_no
START WITH curr = 1;Oracle 11g Release 2 版:
SELECT p.employee_no,
LISTAGG(r.role_name, ',') WITHIN GROUP (ORDER BY r.role_name) AS role_list
FROM employee p, employee_role_mapping m, role r
WHERE p.employee_no = m.employee_no
AND m.role_id = r.role_id
GROUP BY employee_no;順帶一提確認 Oracle 版本的 SQL：
SELECT * FROM V$VERSION;
0 回應 :
張貼留言
讓阿布知道你對這篇文章的想法吧!