oracle中,wm_concat函数是一个聚合函数,和mysql中的group_concat函数类似。
通过wm_concat函数可以实现行转列
NO CLASS SCORE
1001 1 99
1002 2 98
1001 3 97
1004 1 98
1005 1 97
1006 1 99
1007 2 98
1008 3 97
统计 每个CLALSS里的NO
select class,WM_CONCAT(NO) from A_TEST group by class;
CLASS WM_CONCAT(NO)
1 1001,1006,1005,1004
2 1002,1007
3 1001,1008
结果已经出来,如果想对NO进行排序,可以结合over、partition函数
①先排序
select class,WM_CONCAT(NO) over(partition by class order by no) m from A_TEST;
CLASS M
1 1001
1 1001,1004
1 1001,1004,1005
1 1001,1004,1005,1006
2 1002
2 1002,1007
3 1001
3 1001,1008
②取最终结果
select CLASS,MAX(M) from (select class,WM_CONCAT(NO) over(partition by class order by no) m from A_TEST) group by CLASS;
CLASS MAX(M)
1 1001,1004,1005,1006
2 1002,1007
3 1001,1008
如果是MySQL,则用:SELECT id, GROUP_CONCAT(concat_column ORDER BY order_column ASC/DESC) from some_table GROUP BY id;
最后再说一个坑,Oracle的wm_concat有可能是varchar2,也可能是CLOB,如果需要其都变成varchar,就使用TO_CHAR(wm_concat(column) over (partition by group_column order by order_column ASC/DESC))
文章转载(感谢原作者):https://blog.csdn.net/qingdatiankong/article/details/78957934