mysql 中使用函数_Mysql中函数在dm中使用

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