一句話實現欄位拆分成多行

把表中某欄位根據分隔符拆分成N個字串後，再用這N個字串把這一行演變成N行。

用SQL來解決這個問題非常煩瑣！

SQL裡沒有提供集合物件，不能提供根據拆分後的字串集合把一行變成多行的操作。解決這個問題的思路就是先求出欄位拆分後的最大字串個數M，然後構造一個M行1列的臨時表T2，其列名為lv，則各行lv值分別為1,2,……,M，然後用原表與之叉乘，叉乘時取欄位拆分後的第T2.lv個字串。這樣寫出來的SQL是多個子查詢巢狀而成，其語法是比較複雜的。而且各種資料庫中拆分字串的函式並不統一，所以SQL的寫法也各不相同。

舉個例子：現有學生選修課資料表COURSES資料如下，要求查出每個學生選修了幾門課：

COURSE	STUDENTS
Chinese	Tom,Kate,John,Jimmy
Russia	Tom,Cart,Jimmy
Spanish	Kate,Joan,Cart
Portuguese	John,Tom
History	Tom,Cart,Kate
Music	Kate,Joan,Tom

要求輸出結果如下：

STUDENT	NUM
Tom	5
Kate	4
Cart	3
Jimmy	2
Joan	2
John	2

 

以Oracle為例，用SQL寫出來是這樣：

SELECT STUDENT, COUNT(*) NUM FROM

       (SELECT T1.COURSE, REGEXP_SUBSTR(T1.STUDENTS, '[^,]+', 1, T2.LV ) STUDENT

       FROM COURSES T1,

              ( SELECT LEVEL LV

                 FROM (SELECT MAX(REGEXP_COUNT(A.STUDENTS, '[^,]+', 1)) R_COUNT

                         FROM COURSES A

              ) B

               CONNECT BY LEVEL <= B.R_COUNT) T2

        WHERE REGEXP_SUBSTR(T1.STUDENTS, '[^,]+', 1, T2.LV) IS NOT NULL

       ) C

GROUP BY STUDENT

ORDER BY NUM DESC;

 

這裡的C就是前文提到的那個臨時表，可見這個SQL層次很多，可讀性比較差，不易讀懂。

 

如果用集算器的SPL語言來解決這個問題，就會簡單很多，只需1行程式碼：

connect("mydb").query("SELECT * FROM COURSES").news(STUDENTS.split@c();~:STUDENT).groups(STUDENT;count(1):NUM).sort(-NUM)

SPL語言有集合物件，並提供了根據集合把一行擴充套件成多行的功能，所以寫起來思路清晰明瞭，簡便易懂，並且語法統一，不論資料來自哪種資料庫還是來自檔案型資料來源，寫法都是一樣的。

 

SPL 集合還提供了交、差、並運算，聚合運算，迴圈遍歷運算，請閱

SPL也能很方便地嵌入到JAVA應用，可參考 。

具體使用方法可參考 。