深入理解oracle行轉列wm_concat函數

一、wm_concat函數的基礎知識

wm_concat函數是oracle內置的一個字符串聚合函數，它可以將多個行組合成一個字符串，通常用在GROUP BY子句中。wm_concat函數常常被用於將一組數據按照某種方式串聯起來，以便更好地查詢和分析。

wm_concat函數語法如下：

SELECT wm_concat(column_name) FROM table_name;

其中column_name表示需要串聯的列名，table_name表示數據來源表名。使用wm_concat函數時，必須將GROUP BY子句中的其他列也寫入SELECT語句中。

二、wm_concat函數的實例應用

為了更好地理解wm_concat函數的使用方法和應用價值，下面給出一個實例。

假設存在以下學生成績表：

CREATE TABLE student_scores (
  id         NUMBER(10) PRIMARY KEY,
  name       VARCHAR2(50) NOT NULL,
  subject    VARCHAR2(50) NOT NULL,
  score      NUMBER(3,1) NOT NULL
);

INSERT INTO student_scores (id,name,subject,score) VALUES (1,'張三','語文',85);
INSERT INTO student_scores (id,name,subject,score) VALUES (2,'張三','數學',92);
INSERT INTO student_scores (id,name,subject,score) VALUES (3,'李四','語文',79);
INSERT INTO student_scores (id,name,subject,score) VALUES (4,'李四','數學',88);
INSERT INTO student_scores (id,name,subject,score) VALUES (5,'王五','語文',90);
INSERT INTO student_scores (id,name,subject,score) VALUES (6,'王五','數學',96);

現在需要查詢每個學生的成績，按照以下格式進行展示：

張三：語文-85，數學-92
李四：語文-79，數學-88
王五：語文-90，數學-96

使用以下SQL語句可以完成查詢：

SELECT name, wm_concat(subject || '-' || score) AS scores
FROM student_scores
GROUP BY name
ORDER BY name;

運行結果如下：

NAME  | SCORES
----  | ------------
張三  | 數學-92,語文-85
李四  | 數學-88,語文-79
王五  | 數學-96,語文-90

從查詢結果可以看出，wm_concat函數可以將每個學生的成績串聯起來，並用逗號隔開。這種方法大大簡化了查詢的代碼，提高了查詢效率。

三、wm_concat函數的使用限制

雖然wm_concat函數在對於一些小型和簡單的數據庫應用中大有用處，但是它有一些使用限制。下面列出了幾個特別需要注意的限制：

1. wm_concat函數不能用於超大型數據集

由於wm_concat函數使用字符串連接，它的內部處理會導致系統緩存區溢出。因此，當處理數據量超過4K時，wm_concat函數會失敗，因為緩存區最大只有2K。如果需要處理超大型數據集，可以使用其他字符串聚合函數代替。

2. wm_concat函數不會自動過濾重複數據

wm_concat函數不會自動過濾重複數據，因此在使用wm_concat函數時需要自己手動去重。可以使用DISTINCT關鍵字實現去重，例如：

SELECT name, wm_concat(DISTINCT subject || '-' || score) AS scores FROM student_scores

3. wm_concat函數無法處理NULL值

wm_concat函數不能將NULL值附加到串聯字符串中，否則會導致整個串聯操作失敗。為了避免這種情況，可以使用NVL函數將NULL替換為其他非空值，例如：

SELECT name, wm_concat(NVL(subject,'-') || '-' || NVL(score,'-')) AS scores FROM student_scores

四、使用LISTAGG替代wm_concat函數

由於wm_concat函數的使用限制，oracle 11g引入了新的字符串聚合函數LISTAGG。與wm_concat函數相比，LISTAGG函數有一些重要的不同之處。

1. LISTAGG函數不會發生緩存區溢出

與wm_concat函數不同，LISTAGG函數不會因為數據集超過特定的限制而發生緩存區溢出。這是因為LISTAGG函數使用的是LOB字符串，它可以達到4G大小的數據集。

2. LISTAGG函數自動過濾重複值

與wm_concat函數不同，LISTAGG函數可以自動過濾重複數據。不需要手動去重，很大程度上節省了編寫複雜代碼的時間。

3. LISTAGG函數可以處理NULL值

與wm_concat函數不同，LISTAGG函數可以處理NULL值。通過設置WITHIN GROUP子句中的NULL選項，可以控制LISTAGG函數發現的NULL值的處理方式。

下面給出一個使用LISTAGG函數的實例：

SELECT name, LISTAGG(DISTINCT subject || '-' || score, ',') WITHIN GROUP (ORDER BY subject DESC) AS scores FROM student_scores GROUP BY name;