Big Head DBA

幸福在前方，我在路上......

SELECT id FROM test WHERE id<10;
   USER_ID
----------
  1
  2
  3
  4
  5
  6
  7
  8
  9
9 rows selected.

SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(user_id,',')),',') allrow
FROM (SELECT 1,user_id,1+(ROW_NUMBER() over(ORDER BY 1)) node_id,ROW_NUMBER() over(PARTITION BY 1 ORDER BY 1) rn
FROM test WHERE id<20)
START WITH rn = 1
CONNECT BY node_id-1 = PRIOR node_id
GROUP BY 1
ORDER BY 1;
ALLROW
-------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19

CREATE OR REPLACE TYPE strcat_type AS object (
    cat_string VARCHAR2(4000),
    static FUNCTION ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) RETURN NUMBER,
    member FUNCTION ODCIAggregateIterate(self IN OUT strcat_type,VALUE IN VARCHAR2) RETURN NUMBER,
    member FUNCTION ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN OUT strcat_type) RETURN NUMBER,
    member FUNCTION ODCIAggregateTerminate(self IN OUT strcat_type,returnValue OUT VARCHAR2,flags IN NUMBER) RETURN NUMBER
  7  )
  8  
  9  /
TYPE created.
CREATE OR REPLACE FUNCTION strcat(input VARCHAR2 )
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
/
FUNCTION created.
SELECT strcat(a.id) FROM test a WHERE a.user_id<20;
STRCAT(A.USER_ID)
---------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19

mysql> SELECT group_concat( `UID` SEPARATOR  ',' ) AS allrow FROM `test` WHERE id<10;
+-------------------+
| allrow            |
+-------------------+
| 1,2,3,4,5,6,7,8,9 | 
+-------------------+
1 ROW IN SET (0.00 sec)