1.wm_concat
字符串拼接函数,默认是以逗号结尾进行分割,并且无法改变。示例:
select
template_id,
wm_concat(template_content) as "templateContent"
from SMS_TEMPLATE_ITEM
group by template_id;
2.listagg
字符串拼接函数,由于wm_concat是只能以逗号结尾进行分割,在实际应用中有些许不变,因此又写了一个函数能够灵活变通。
格式:<LISTAGG>(<参数>[,<参数>]) WITHIN GROUP (<ORDER BY 项>)
示例:
select
TEMPLATE_ID,
listagg(template_content,';') WITHIN group (order by TEMPLATE_ID)
from SMS_TEMPLATE_ITEM
group by TEMPLATE_ID;
2.创建联合唯一约束
示例:
alter table table_name add constraint AGE_NAME_UNIQUE unique (AGE,NAME);
删除唯一约束
alter table COUNTER_DATA drop constraint TERM_NO_REP_DATE;
3.达梦使用oracle的函数方式实现将字符串按照某个符号分割成多行
参考:如何在Oracle中将字符串拆分为多行? - 问答 - 云+社区 - 腾讯云 (tencent.com)
WITH
temp AS
(
SELECT 108 NAME, 'test' Project, 'Err1, Err2, Err3' Error FROM DUAL
UNION ALL
SELECT 109, 'test2' Project, 'Err1' FROM DUAL
)
SELECT DISTINCT
NAME ,
Project,
trim( regexp_substr ( str, '[^,]+', 1, LEVEL )) str
FROM
(
SELECT NAME, Project, Error str FROM temp
)
t CONNECT BY instr( str, ',', 1, LEVEL - 1 ) > 0
ORDER BY
NAME