Oracle中的列转行实现字段拼接用例

文章目录

Oracle中的列转行实现字段拼接

场景

在SQL使用过程中经常有这种需求：将某列字段拼接成in('XX','XX','XX','XX','XX','XX' ...)做为查询条件。

实现

select 'in ('''||replace(wm_concat(EMPNO),',',''',''')||''')' from EMP

总结

两端字符的拼接

这里用'in ('''和''')'分别实现了in ('和')的前后拼接，以''')'为例，其中第一个第四个'是Oracle中的字符串连接符，第二个'是一个转义字符,第三个'才是我们真正被转义的内容。

lpad()、rpad()在两端字符的填充中较难控制填充字符串长度。

wm_concat的使用

这里推荐使用LISTAGG()函数,主要因为：

在不同的oracle版本中，WMSYS.WM_CONTACT返回的数据类型不一致，10G版本中，返回的是字符串类型，11G版本中，返回的是clob类型,Oracle 11gR2和12C上已经摒弃了wm_concat函数。

LISTAGG()支持自定义分割字符串，默认值为 NULL，而WMSYS.WM_CONTACT固定只能用'拼接，除非使用REPLACE()替换为不同拼接符。

select  mgr,'in (''' ||REPLACE(LISTAGG(EMPNO, ',') within group(order by mgr), ',', ''','& 大专栏  Oracle中的列转行实现字段拼接用例#39;') || ''')' from EMP group by mgr

扩展

转义符

'（单引号）的转义

Select 'test' || '''' from dual;

Select 'test ''' from dual;

ASCII码实现转义

Select 'Tom' || chr(38) || 'Jerry' from dual;  --chr(38)是‘&'的ASCII码

ESCAPE转义

使用 ESCAPE关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。

SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%%'; -- 查找全部

SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%_%'; -- 查找全部

SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%/%'; -- 查找含有左斜杠

SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%/%%' ESCAPE '/'; -- 使用/转义，查找含有%的字符

SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%/_%' ESCAPE '/'; -- 使用/转义，查找含有_的字符

LISTAGG函数

语法

  LISTAGG(aggregate_expression [, 'delimiter' ] )  [ WITHIN GROUP (ORDER BY order_list) ]

参数

aggregate_expression:
提供要聚合的值的任何有效表达式（如列名称）。忽略 NULL 值和空字符串。LISTAGG 不支持 DISTINCT 表达式。

delimiter:
可选。将分隔串联的值的字符串常数。默认值为 NULL。

WITHIN GROUP (ORDER BY order_list):
可选。指定聚合值的排序顺序。

返回值

VARCHAR(MAX)。如果结果集大于最大 VARCHAR 大小（64K – 1 或 65535），则 LISTAGG 返回以下错误：

  Invalid operation: Result size exceeds LISTAGG limit

巴特西

Oracle中的列转行实现字段拼接用例

场景

实现

总结

扩展

转义符

LISTAGG函数

最新文章

热门文章