SQL> select * from t_test;需求为:得到根据T_TYPE聚合,并且根据T_ID排序的一个集合值。
T_ID T_NAME T_TYPE
---------- -------------------- --------------------
1 保定 河北
2 邯郸 河北
8 韶关 广东
7 东莞 广东
6 深圳 广东
理论值为:普通语句为:
T_TYPE T_NAME
---------- --------------------
广东 深圳,东莞,韶关
河北 保定,邯郸
SQL> col t_name format a20
SQL> SELECT T_TYPE,TO_CHAR(WM_CONCAT(T_NAME)) T_NAME
2 FROM T_TEST
3 GROUP BY T_TYPE;
T_TYPE T_NAME
---------- --------------------
广东 韶关,深圳,东莞
河北 保定,邯郸
结果发现,T_NAME为乱序排列,无法满足要求,那如果例如子查询先排序,再聚合呢?
SQL> SELECT T_TYPE,TO_CHAR(WM_CONCAT(T_NAME)) T_NAME
2 FROM (SELECT * FROM T_TEST ORDER BY T_ID)
3 GROUP BY T_TYPE;
T_TYPE T_NAME
---------- --------------------
广东 深圳,韶关,东莞
河北 保定,邯郸
发现还是不行,即使子查询是排序的,wm_concat也不按顺序聚合。下面为解决方法!
********************************************************************************************************************************
解决方法一:
SQL> SELECT DISTINCT T_TYPE,
2 TO_CHAR(WM_CONCAT(T_NAME)
3 OVER(PARTITION BY T_TYPE ORDER BY T_ID
4 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) T_NAME
5 FROM T_TEST;
T_TYPE T_NAME
---------- --------------------
广东 深圳,东莞,韶关
河北 保定,邯郸
SQL> SELECT T_TYPE, T_NAME
2 FROM (SELECT T_TYPE,
3 TO_CHAR(WM_CONCAT(T_NAME)
4 OVER(PARTITION BY T_TYPE ORDER BY T_ID
5 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) T_NAME
6 FROM T_TEST)
7 GROUP BY T_TYPE, T_NAME;
T_TYPE T_NAME
---------- --------------------
广东 深圳,东莞,韶关
河北 保定,邯郸
解决方法二:
SQL> SELECT T_TYPE, MAX(TO_CHAR(T_NAME)) T_NAME
2 FROM (SELECT T_TYPE,
3 WM_CONCAT(T_NAME) OVER(PARTITION BY T_TYPE ORDER BY T_ID) T_NAME
4 FROM T_TEST)
5 GROUP BY T_TYPE;
T_TYPE T_NAME
---------- --------------------
广东 深圳,东莞,韶关
河北 保定,邯郸
解决方法三(如果数据量不大的话,可以用树形查询来做):
SQL> SELECT T_TYPE, SUBSTR(MAX(SYS_CONNECT_BY_PATH(T_NAME, ',')), 2) T_NAME
2 FROM (SELECT T_NAME,
3 T_ID,
4 T_TYPE,
5 ROW_NUMBER() OVER(PARTITION BY T_TYPE ORDER BY T_ID) RN
6 FROM T_TEST)
7 START WITH RN = 1
8 CONNECT BY RN = PRIOR RN + 1
9 AND T_TYPE = PRIOR T_TYPE
10 GROUP BY T_TYPE;
T_TYPE T_NAME
---------- --------------------
广东 深圳,东莞,韶关
河北 保定,邯郸