{"id":18394,"date":"2026-05-26T14:32:31","date_gmt":"2026-05-26T06:32:31","guid":{"rendered":"https:\/\/kbwp.transwarp.cn\/?p=18394"},"modified":"2026-05-26T14:34:06","modified_gmt":"2026-05-26T06:34:06","slug":"oracle-listagg-%e8%af%ad%e6%b3%95%e5%9c%a8quark%e7%9a%84%e6%94%b9%e5%86%99","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=18394","title":{"rendered":"oracle listagg \u8bed\u6cd5\u5728quark\u7684\u6539\u5199"},"content":{"rendered":"

\u6982\u8981\u63cf\u8ff0<\/h3>\n
\n

quark\u4e2d\u4e0d\u652f\u6301listagg\u51fd\u6570\uff0c\u53ef\u4ee5\u4f7f\u7528\u5176\u4ed6\u51fd\u6570\u8fdb\u884c\u6539\u5199\uff0c\u672c\u6587\u7ed9\u51fa\u89e3\u91ca\u8bf4\u660e\u3002<\/p>\n

\u8be6\u7ec6\u4ecb\u7ecd<\/h3>\n
\n

oracle\u8bed\u6cd5<\/h4>\n
with tb as\n(select 1 as deptno,'zhangsan' as name from dual union all\nselect 1 as deptno,'lisi' as name from dual union all\nselect 1 as deptno,'lisi' as name from dual union all\nselect 2 as deptno,'wangwu' as name from dual  union all\nselect 2 as deptno,'wangwu' as name from dual)\nselect\n       distinct    --\u4f7f\u7528distinct\u7684\u8bdd\u4f1a\u53bb\u91cd\uff0c\u65b9\u6cd51\u548c\u65b9\u6cd52\u652f\u6301\uff1b\u5982\u679c\u4e0d\u9700\u8981\u53bb\u91cd\uff0c\u8003\u8651\u65b9\u6cd53\n       deptno,\n       listagg(name,'\/') within group (order by name desc) over (partition by deptno ) as total_ename\nfrom tb;<\/code><\/pre>\n
\n  \n<\/div>\n
quark\u6539\u5199<\/h4>\n
\u65b9\u6cd5\u4e00\uff1aconcat_ws\u7ed3\u5408collect_list<\/h5>\n
\u5347\u5e8f\uff1a<\/strong><\/p>\n
set character.literal.as.string=TRUE;\n\nwith tb as\n(select 1 as deptno,'zhangsan' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 2 as deptno,'wangwu' as name from system.dual  union all\nselect 2 as deptno,'wangwu' as name from system.dual)\nselect\n       deptno,\n       CONCAT_WS(',',sort_array(COLLECT_LIST(name)))\nfrom tb\nGROUP BY deptno;<\/code><\/pre>\n
\n  \n<\/div>\n
\u964d\u5e8f\uff1a<\/strong><\/p>\n
\n
WARP-136509 sort_array\u51fd\u6570\u652f\u6301\u964d\u5e8f\uff0c \u9a8c\u8bc1\u65b9\u5f0f\uff1a SELECT sort_array(array(3,1,4,5,2),false) AS result FROM system.dual;<\/p>\n<\/blockquote>\n
set character.literal.as.string=TRUE;\n\nwith tb as\n(select 1 as deptno,'zhangsan' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 2 as deptno,'wangwu' as name from system.dual union all\nselect 2 as deptno,'wangwu' as name from system.dual)\nselect\ndeptno,\nCONCAT_WS(',',sort_array(COLLECT_LIST(name),false))\nfrom tb\nGROUP BY deptno;<\/code><\/pre>\n
\n  \n<\/div>\n
\u65b9\u6cd5\u4e8c\uff1agroup_concat\u7ed3\u5408group by<\/h5>\n
\n
\u53bb\u91cd\uff1b\u65e0\u6cd5\u6392\u5e8f\uff0c\u4e14\u4e0d\u50cf\u5f00\u6e90hive\u652f\u6301group_concat(distinct name ORDER BY name DESC SEPARATOR ‘,’)<\/p>\n<\/blockquote>\n
set character.literal.as.string=TRUE;\n\nwith tb as\n(select 1 as deptno,'zhangsan' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 2 as deptno,'wangwu' as name from system.dual  union all\nselect 2 as deptno,'wangwu' as name from system.dual)\nselect\n       deptno,\n       group_concat(name, ',')\nfrom tb\ngroup by deptno;<\/code><\/pre>\n
\n  \n<\/div>\n
\u65b9\u6cd5\u4e09\uff1agroup_concat\u7ed3\u5408partition by\u7684\u65b9\u6cd5<\/h5>\n
\n
\u4e0d\u53bb\u91cd\uff1b\u65e0\u6cd5\u6392\u5e8f\uff0c\u4e14\u4e0d\u50cf\u5f00\u6e90hive\u652f\u6301group_concat(distinct name ORDER BY name DESC SEPARATOR ‘,’)<\/p>\n<\/blockquote>\n
set character.literal.as.string=TRUE;\n\nwith tb as\n(select 1 as deptno,'zhangsan' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 1 as deptno,'lisi' as name from system.dual union all\nselect 2 as deptno,'wangwu' as name from system.dual  union all\nselect 2 as deptno,'wangwu' as name from system.dual)\nselect\n       deptno,\n       group_concat(name, ',') over (partition by deptno )\nfrom tb;<\/code><\/pre>\n
\n  \n<\/div>\n","protected":false},"excerpt":{"rendered":"
\u6982\u8981\u63cf\u8ff0 quark\u4e2d\u4e0d\u652f\u6301listagg\u51fd\u6570\uff0c\u53ef\u4ee5\u4f7f\u7528\u5176\u4ed6\u51fd\u6570\u8fdb\u884c\u6539\u5199\uff0c\u672c\u6587\u7ed9\u51fa\u89e3\u91ca\u8bf4\u660e\u3002 \u8be6\u7ec6\u4ecb\u7ecd ora ..<\/p>\n
<\/div>\n
Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-18394","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/18394","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18394"}],"version-history":[{"count":3,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/18394\/revisions"}],"predecessor-version":[{"id":18590,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/18394\/revisions\/18590"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18394"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=18394"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=18394"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}