Mysql中函数在dm中使用
一、问题
1 、mysql中存在FIND_IN_SET函数，但是在dm中是不存在的。解决办法手动创建FIND_IN_SET（）函数在dm是可以实现的，并调用使用的。但是当结果集比较大的时候，效率很低。数据量小的时候可以使用。当时数据量比较大的时候使用FIND_IN_SET函数，在dm使用会出现字符集截断的问题。基于以上的问题。在dm使用instr（）函数代替FIND_IN_SET函数。可以解决效率低和字符串截断的问题问题。
2 、mysql中存在 group_concat()函数。但在dm里是没有的。可以使用oracle中wm_concat或者dm的LISTAGG/LISTAGG2 集函数来代替。Dm中LISTAGG/LISTAGG2 集函数和oracle中LISTAGG/LISTAGG2 集函数使用方法是一样的。
二、FIND_IN_SET函数
1 、Mysql的定义 ：
MySQL 手册中find_in_set函数的语法解释：
FIND_IN_SET(str,strlist) 。str 要查询的字符串 strlist 参数以,分隔的字段名 如 (1,2,6,8,10,22)查询字段(strlist)中包含(str)的结果，返回结果为null或记录假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间 一个字符串列表就是一个由一些被 , 符号分开的子链组成的字符串如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算 如果str不在strlist 或strlist 为空字符串，则返回值为 0 如任意一个参数为NULL，则返回值为 NULL这个函数在第一个参数包含一个逗号(,)时将无法正常运行。
SELECT FIND_IN_SET('b', 'a,b,c,d') ；结果为:2因为b 在strlist集合中放在2的位置 从1开始select FIND_IN_SET('1', '1'); 返回 就是1 这时候的strlist集合有点特殊 只有一个字符串 其实就是要求前一个字符串 一定要在后一个字符串集合中才返回大于0的数。select FIND_IN_SET('2', '1，2'); 返回2 。select FIND_IN_SET('6', '1'); 返回0 strlist中不存在str，所以返回0。
2 、自定义的 find_in_set （）
|
create or replace function FIND_IN_SET2 ( piv_str1 varchar2 , piv_str2 varchar2 ) return int deterministic as rt int default 0 ; begin with a as ( select regexp_substr ( sqlstr , '[^,]+' , 1 , level ) rval , rownum rid from ( select piv_str2 sqlstr from dual ) connect by level <= regexp_count ( sqlstr , ',' ) + 1 ) select nvl ( rid , 0 ) into rt from dual left join ( select rid from a where rval = piv_str1 ) on 1 = 1 ; return rt ; end ; |
3 、网上配置在oracle 中的find_in_set （）函数定义
|
CREATE or replace FUNCTION "FIND_IN_SET" ( piv_str1 varchar2 , piv_str2 varchar2 , p_sep varchar2 : = ',' ) RETURN NUMBER IS l_idx number : = 0 ; -- 用于计算 piv_str2 中分隔符的位置 str varchar2 ( 500 ); -- 根据分隔符截取的子字符串 piv_str varchar2 ( 500 ) : = piv_str2 ; -- 将 piv_str2 赋值给 piv_str res number : = 0 ; -- 返回结果 loopIndex number : = 0 ; BEGIN -- 如果 piv_str 中没有分割符，直接判断 piv_str1 和 piv_str 是否相等，相等 res=1 IF instr ( piv_str , p_sep , 1 ) = 0 THEN IF piv_str = piv_str1 THEN res: = 1 ; END IF ; ELSE -- 循环按分隔符截取 piv_str LOOP l_idx : = instr ( piv_str , p_sep ); loopIndex: = loopIndex + 1 ; -- 当 piv_str 中还有分隔符时 IF l_idx > 0 THEN -- 截取第一个分隔符前的字段 str str: = substr ( piv_str , 1 , l_idx - 1 ); -- 判断 str 和 piv_str1 是否相等，相等 res=1 并结束循环判断 IF str = piv_str1 THEN res: = loopIndex ; EXIT ; END IF ; piv_str : = substr ( piv_str , l_idx + length ( p_sep )); ELSE -- 当截取后的 piv_str 中不存在分割符时，判断 piv_str 和 piv_str1 是否相等，相等 res=1 IF piv_str = piv_str1 THEN res: = loopIndex ; END IF ; -- 无论最后是否相等，都跳出循环 EXIT ; END IF ; END LOOP ; -- 结束循环 END IF ; -- 返回 res RETURN res ; END FIND_IN_SET ; |
4 、函数调用案例 find_in_set （）进行查询数据，报字符串截断。
使用instr替代后下效果如下
使用instr替代后下效果如下
使用instr替代后下效果如下
三、group_concat()函数
1 、mysql 中group_concat() 函数的定义
功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
2 、在dm 用wm_concat 或者dm 的LISTAGG/LISTAGG2 集函数
1、wm_concat
介绍：其函数在Oracle 10g推出，在10g版本中，返回字符串类型，在11g版本中返回clob类型。
括号里面的参数是列，而且可以是多个列的集合，也就是说在括号里面可以自由地用‘||’合并字符串。
用法1： Select aaa, wmsys.wm_concat(bbb || '(' || ccc || ')' ) from table group by aaa
用法2： Select aaa, wmsys.wm_concat(bbb || '(' || ccc || ')' ) over(partition by aaa) from table
举例：
2、LISTAGG() 简介
介绍：其函数在Oracle 11g 版本中推出，对分组后的数据按照一定的排序进行字符串连接。
其中，“[,]”表示字符串连接的分隔符，如果选择使用[over (partition by )]则会使其变成分析函数；
用法1： SELECT aaa, listagg(bbb,',') within GROUP (ORDER BY aaa) FROM table GROUP BY aaa
用法2： SELECT aaa, listagg(bbb,',') within GROUP (ORDER BY aaa) over(partition by aaa) FROM table
举例：listagg(ORGAN_CODE, ',')within group( order by1) listagg
