WM_CONCAT()是合并列函数,能够快速的帮助我们将列变为行进行展示。
比如:
select b.FUNCTIONCODE,c.ENCODE FROM TR_SYS_UF a
LEFT JOIN TD_SYS_FUNCTION b ON a.FUNCTIONID = b.FUNCTIONID AND b.FLAG = 1
LEFT JOIN TD_SYS_MENU c ON b.MENUID = c.MENUID AND c.FLAG = 1
WHERE a.FLAG = 1
结果为:
使用WM_CONCAT函数后
select WM_CONCAT(b.FUNCTIONCODE) FUNCTIONCODE,c.ENCODE FROM TR_SYS_UF a
LEFT JOIN TD_SYS_FUNCTION b ON a.FUNCTIONID = b.FUNCTIONID AND b.FLAG = 1
LEFT JOIN TD_SYS_MENU c ON b.MENUID = c.MENUID AND c.FLAG = 1
WHERE a.FLAG = 1
GROUP BY c.ENCODE
结果为:
oracle 后期版本WM_CONCAT()就被摒弃了,如果还想达到上述效果则可以使用LISTAGG()函数。
以上述结果为例
使用LISTAGG()
select LISTAGG(b.FUNCTIONCODE,',') WITHIN GROUP(ORDER BY b.FUNCTIONID) FUNCTIONCODE ,c.ENCODE FROM TR_SYS_UF a
LEFT JOIN TD_SYS_FUNCTION b ON a.FUNCTIONID = b.FUNCTIONID AND b.FLAG = 1
LEFT JOIN TD_SYS_MENU c ON b.MENUID = c.MENUID AND c.FLAG = 1
WHERE a.FLAG = 1
GROUP BY c.ENCODE
结果为
效果相同。