wm_concat函数的排序问题

来源：互联网发布：mysql删除and语句编辑：程序博客网时间：2024/05/07 12:33

测试数据显示如下：

SQL> select * from t_test;

      T_ID T_NAME               T_TYPE
---------- -------------------- --------------------
         1 保定                 河北
         2 邯郸                 河北
         8 韶关                 广东
         7 东莞                 广东
         6 深圳                 广东

需求为：得到根据T_TYPE聚合，并且根据T_ID排序的一个集合值。

    理论值为：

    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
---------- --------------------
广东       深圳,东莞,韶关
河北       保定,邯郸

0 0