PGSQL下的常用函数

1、COALESCE — 空值替换函数。
ExportDocumenttaskMapper.xml.listBusiInfos4Export                    ---coalesce(cbi.valid_month, '999999')
IntegrationPointConfigMapper.xml.getIntegrationPointConfigList         ---round(coalesce(pc.deduction_value,0) * 100,2)||'%'
2、regexp_split_to_table — 行专列
示例：regexp_split_to_table(col, 'splitor')：如果某条记录的col列的值为‘1,2,3,4,5'，而splitor为','，则结果是：该条记录被转换成5条记录，且各条记录的col列的值依次为1、2、3、4、5
select * from regexp_split_to_table('kenyon,love,1,china,!',',');
CmNowManagerCommissionMapper.xml.selectAllCommission
3、regexp_split_to_array


select string_to_array('1,2,3,4,5,6',',') arr from dual;
select regexp_split_to_array('1,2,3,4,5,6',',') arr from dual;
select * from regexp_split_to_array('1,2,3,4,5,6',',');
select regexp_split_to_table('1,2,3,4,5,6',',') bb from dual;
select * from dual where '1' in (select * from regexp_split_to_table('1,2,3,4,5,6',','));

 select * from dual where '1' in (SELECT
            UNNEST (
                regexp_split_to_array('1,2,3,4,5,6',',')
            ));
 select * from dual where '1' in (SELECT
            UNNEST (
                string_to_array('1,2,3,4,5,6',',')
            ));
UNNEST--表示把array类型展开成多行
string_to_array和regexp_split_to_array效果基本一致
CommBilloneApplReview_Column_List
regexp_split_to_array(d.business_type,',')@>array[t.businessType || '']

转载于:https://www.cnblogs.com/fengyouheng/p/10266779.html