連線字串的SQL方法

我的部落格的第一篇文章就是介紹如何將字串連線在一起的，這裡介紹一下透過SQL的方法來解決同樣的問題。

對字串的“sum”——在Oracle中自定義聚集函式的例子：http://yangtingkun.itpub.net/post/468/3380

對於下面這個利用自定義聚集函式的例子，將其改寫為直接用SQL實現：

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
-------------------------------------------------- ------- ----------
SY_NAME SYNONYM
T TABLE
TEST TABLE
TEST1 TABLE
T_BLOB TABLE
T_CLOB TABLE
V_T VIEW
V_TEST VIEW

已選擇8行。

SQL> SELECT TABTYPE, F_LINK(TNAME) TNAME FROM TAB GROUP BY TABTYPE;

TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST1,T_CLOB,T_BLOB,TEST
VIEW V_T,V_TEST

除了利用自定義聚集函式外，SQL函式中能將多個字串合併在一起的只有SYS_CONNECT_BY_PATH了。

而這個函式只能應用在樹型查詢中，為了能使用這個函式，必須人為的構造出樹來。也就是說，必須可以構造出一個CONNECT BY列使得相同的TABTYPE的TNAME可以用SYS_CONNECT_BY_PATH連線起來。

連線列可以使用ROW_NUMBER() OVER()來構造，在CONNECT BY的時候指定當前列的等於父列的值加1。並在START WITH時指定起始值為1。

最後對TABTYPE進行分組，取得最大值就是最終需要的結果：

SQL> SELECT TABTYPE, MAX(LTRIM(SYS_CONNECT_BY_PATH(TNAME, ','), ',')) TNAME
2 FROM
3 (
4 SELECT TABTYPE, TNAME, ROW_NUMBER() OVER(PARTITION BY TABTYPE ORDER BY TNAME) RN
5 FROM TAB
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 AND PRIOR TABTYPE = TABTYPE
10 GROUP BY TABTYPE;

TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST,TEST1,T_BLOB,T_CLOB
VIEW V_T,V_TEST