Oracle
1、LISTAGG 函数
LISTAGG(字段, 连接符) WITHIN GROUP (ORDER BY 字段)
例如:
LISTAGG(t.id || t.rownum, ',') WITHIN GROUP (ORDER BY t.type)
LISTAGG(t.id || t.rownum, '_') AS column
SELECT
t.type,
LISTAGG(t.id || t.rownum, ',') WITHIN GROUP (ORDER BY t.type) AS column1,
LISTAGG(t.id || t.rownum, '_') AS column2
FROM
my_table t
GROUP BY
t.type;
默认不存在连接符
2、XMLAGG 函数 针对大量的数据 最后形成的列为 clob类型
XMLAGG(XMLPARSE(CONTENT 字段 || 字符串 WELLFORMED) ORDER BY 字段).GETCLOBVAL()
例如:
XMLAGG(XMLPARSE(CONTENT t.id || '_' || t.rownum || ',' WELLFORMED) ORDER BY t.type).GETCLOBVAL()
XMLAGG(XMLPARSE(CONTENT t.id || '_' || t.rownum || ',' WELLFORMED)).GETCLOBVAL() AS column
SELECT
t.type,
XMLAGG(XMLPARSE(CONTENT t.id || '_' || t.rownum || ',' WELLFORMED) ORDER BY t.type).GETCLOBVAL() AS column1,
XMLAGG(XMLPARSE(CONTENT t.id || '_' || t.rownum || ',' WELLFORMED)).GETCLOBVAL() AS column AS column2
FROM
my_table t
GROUP BY
t.type
分隔符为自己拼接的最后一个字符串 XMLPARSE(CONTENT t.id || '_' || t.rownum || ',' WELLFORMED
MySQL
1、group_concat 函数
例如:
group_concat(t.id) as column
group_concat(t.id,'_',t.name)
分隔符默认为逗号(,)
可以自定义分割符 group_concat(t.id,t.name separator "_")
SELECT
group_concat(t.id) AS column1,
group_concat(t.id,'_',t.name) AS column2,
group_concat(t.id,'_',t.name separator '____')
FROM
my_table t
GROUP BY
t.type