先前文章曾探討兩大資料庫MSSQL及ORACLE已分別提供STRING_AGG、LISTAGG字串彙總分析函數,如下表所示,但兩者均未支援DISTINCT子句的使用,另外,分析函數是不允許互相嵌套(Nested),因此採用子查詢方式為目前較為合宜作法,字串彙總分析函數為彙總類函數,如子查詢中搭配DISTINCT使用,則恐造成彙總運算不正常問題。在此將利用ROW_NUMBER次序函數及NULL資料運算概念克服此問題。
MSSQL 2017
|
ORACLE 11g R2
|
|
函數名稱
|
STRING_AGG
|
LISTAGG
|
分隔符號參數
|
必要參數
|
選擇參數。預設逗號
|
WITHIN GROUP關鍵字
|
選擇參數
|
必要參數
|
ORDER BY子句
|
選擇參數
|
必要參數
|
輸出型態
|
*與第一個參數相同
通常為8000 bytes
|
varchar2
4000 bytes
|
GROUP BY子句(彙總)
|
需搭配使用
|
需搭配使用
|
DISTINCT關鍵字
|
不支援
|
不支援
|
OVER子句
|
不支援
|
支援
|
以下範例中,每個部門中同仁以CSV字串串連呈現,但資料可能有多筆重覆,希望呈現結果如下
資料
|
希望呈現
|
|
![]() |
![]() |
將利用先前文章中所探討之函數特性達成此功能,SQL如下:
SELECT
Dept
, STRING_AGG(CASE WHEN SEQ =1 THEN EmpName END, ',') WITHIN
GROUP (ORDER BY EmpName) Emp_List
--重點2: 保留第一筆資料,其他相值資料將轉為NULL
, COUNT(DISTINCT
EmpName) Ps_Cnt
FROM
(
SELECT Dept
, EmpName
, ROW_NUMBER() OVER(PARTITION BY Dept, EmpName
ORDER BY GETDATE()
--重點1:由於已無適當欄位可供排序,可用GETDATE()
) SEQ
FROM (VALUES('HR', 'Jane')
, ('MA', 'May')
, ('MA', 'James')
, ('IT', 'Kevin')
, ('IT', 'Andy')
, ('HR', 'Jane')
, ('IT', 'Kevin')
) AS Emp(Dept, EmpName)
) A
GROUP BY Dept
SQL二項重點:
1. 取得第一筆(ROW_NUMBER次序函數)
此案例中,由於已無適當欄位可供排序,但排序對ROW_NUMBER次序函數中為必要項目,因此可用《如何在無任何適當ORDER BY欄位情況下,使用ROW_NUMBER函數》文章中所探討,MSSQL可使用GETDATE()函數,ORACLE則可使用NULL、SYSDATE等。
2. 保留第一筆資料
#
|
SQL
|
1
|
CASE WHEN SEQ =1 THEN EmpName END
|
2
|
CASE WHEN SEQ =1 THEN EmpName ELSE NULL END
|
ORACLE概念相同,SQL如下:
SELECT Dept
--重點2: 保留第一筆資料,其他相值資料將轉為NULL
, COUNT(*) Row_Cnt
, COUNT(DISTINCT EmpName) Ps_Cnt
FROM
(
SELECT Dept
, EmpName
, ROW_NUMBER() OVER(PARTITION BY Dept, EmpName
ORDER BY SYSDATE
--重點1:由於已無適當欄位可供排序,可用GETDATE()
) SEQ
FROM
(
SELECT 'IT', 'Kevin' FROM DUAL UNION ALL
SELECT 'MA', 'May' FROM DUAL UNION ALL
SELECT 'MA', 'James' FROM DUAL UNION ALL
SELECT 'IT', 'Kevin' FROM DUAL UNION ALL
SELECT 'IT', 'Andy' FROM DUAL UNION ALL
SELECT 'HR', 'Jane' FROM DUAL UNION ALL
SELECT 'IT', 'Kevin' FROM DUAL
)
)
GROUP BY Dept
沒有留言:
張貼留言