case when 使用

SELECT DISTINCT
  b.asset_code AS '付款确认书编号',
    b.core_enterprise_name AS '核心企业',
    b.plan_or_bank_name  AS '金融机构',
    b.special_plan_periods AS '期数',
    b.due_date AS '应收账款到期日',
    b.creditor AS '供应商',
    b.debtor AS '项目公司',
    inv.invoice_no AS '发票号码',
    inv.invoice_code AS '发票代码',
    inv.open_date AS '开票日期',
    inv.TAX_EXCLUDING_AMOUNT AS '发票金额（不含税）',
    inv.VERIFICATION_CODE AS '校验码后6位',
    
    inv.invoice_amount AS '发票金额',
    b.factoring_money AS '应收账款金额',
    rel.used_amount AS '单张发票转让金额',
    
    (
CASE
    WHEN exp.state = '0' THEN
    '正常' 
    WHEN exp.state = '1' THEN
    '失控' 
    WHEN exp.state = '2' THEN
    '作废' 
    WHEN exp.state = '3' THEN
    '红冲' 
    WHEN exp.state = '4' THEN
    '异常' 
    WHEN exp.state = '5' THEN
    '检测失败' 
END 
    ) AS '状态',
    exp.err_reason AS '核验失败原因',

    exp.db_create_sys_time AS '检测时间'
    FROM
        biz_exception_invoice_info exp
        
        INNER JOIN ASSET_INVOICE_INFO INV on exp.invoice_no = inv.invoice_no  and  exp.invoice_code = inv.invoice_code   
    INNER JOIN ASSET_INFO_INVOICE_REL REL ON REL.ASSET_INVOICE_INFO_ID = INV.KEY_ID
        INNER JOIN   (
    SELECT
         *
    FROM
        ASSETMGR.V_ASSET_POOL_BANK  
    WHERE
        BACK_STATUS <> 2 
        AND TENANT_ID = 1 
        AND LOAN_DATE_FACTORING IS NOT NULL 
        AND LOAN_DATE_FACTORING <> '' 
        AND LOAN_DATE_FACTORING <> '1900-01-01 00:00:00' 
        AND DUE_DATE > CURDATE() 
        UNION
    SELECT
         *
    FROM
        ASSETMGR.V_ASSET_POOL_ABS   
    WHERE
        BACK_STATUS <> 2 
        AND TENANT_ID = 1 
        AND LOAN_DATE_FACTORING IS NOT NULL 
        AND LOAN_DATE_FACTORING <> '' 
        AND LOAN_DATE_FACTORING <> '1900-01-01 00:00:00' 
        AND DUE_DATE > CURDATE() 
    ) B ON REL.ASSET_INFO_ID = B.ASSET_INFO_ID 


    WHERE
        exp.for_who = 'hegui20210609-3'
        and BACK_STATUS <> 2 
        AND b.TENANT_ID = 1 
        AND b.LOAN_DATE_FACTORING IS NOT NULL 
        AND b.LOAN_DATE_FACTORING <> '' 
        AND b.LOAN_DATE_FACTORING <> '1900-01-01 00:00:00' 
        AND b.DUE_DATE > CURDATE() 
        and inv.del_status=0
        and rel.del_status=0
ORDER BY b.asset_code,exp.invoice_no ASC

    /**
     * 去掉字符串中的空格、回车、换行符、制表符
     * 
     * @param str
     *            字符串
     * @return 去除后的字符串
     * @throws Exception
     */
    public String replaceBlank(String str) throws Exception {
        // 替换字符串中的空格、回车、换行符、制表符
        str = str.replaceAll("\\s*|\t|\r|\n", "");
        // 去掉全角空格
        str = str.replaceAll("[\\s\\p{Zs}]+", "");
        return str;
        
    }

select a.task_id, substring_index(substring_index(a.role_name, ',', b.id + 1), ',', -1) role_name
        from (select m.task_id,
                     case
                         when substr(m.role_name, length(m.role_name)) = ','
                             then substr(m.role_name, 1, length(m.role_name) - 1)
                         else m.role_name end role_name
              from bpm_task m
              where m.role_name != "") a
                 join incre_table b on b.id < (length(a.role_name) - length(replace(a.role_name, ',', '')) + 1)
        order by a.task_id
        
        
SELECT
    (
        CASE h.model_id
        WHEN 1 THEN
            '1'
        WHEN 3 THEN
            '1'
        ELSE
            0
        END
    ) AS urgent_flag,
    b.task_name AS task_name,
    b.process_instance_id AS source_instance_id,
    date_format(
        b.create_time,
        '%Y-%c-%d %H:%i:%s'
    ) AS start_date,
    b. STATUS AS state,
    b.task_id AS task_id,
    a.send_bpo_flag AS bpo_status,
    a.asset_info_id AS assets_id,
    c.biz_data,
    e.process_name,
    g.asset_code
FROM
    gl_bpm.bpm_task b
INNER JOIN gl_bpm.bpm_process_instance d ON d.instance_id = b.process_instance_id
INNER JOIN asset_pool_oper_info a ON a.asset_info_id = d.biz_id
INNER JOIN gl_bpm.bpm_process_definition e ON d.process_definition_id = e.process_definition_id
INNER JOIN auto_audit_biz_data c ON a.asset_info_id = c.assets_id
AND c.del_status = 0
INNER JOIN auto_audit_assets_config_info f ON f.assets_id = a.asset_info_id
INNER JOIN asset_info g ON g.key_id = a.asset_info_id
LEFT JOIN ot_order_urgent h ON h.asset_code = g.asset_code
AND h.del_status = 0
AND h.tenant_id = 1
WHERE
    a.back_status IN (0, 3, 6)
AND f.audit_end_flag = 0
AND f.audit_user_ids NOT LIKE '%,2105181142584270001,%'
AND
IF (
    f.submit_feedback_user_id = '',
    TRUE,
    f.submit_feedback_user_id = '2105181142584270001'
)
AND a.del_status = 0
AND d.del_status = 0
AND e.del_status = 0
AND g.del_status = 0
AND b.del_status = 0
AND f.del_status = 0
AND b.tenant_id = 1
AND d.tenant_id = 1
AND a.tenant_id = 1
AND e.tenant_id = 1
AND c.tenant_id = 1
AND f.tenant_id = 1
AND g.tenant_id = 1
AND c.biz_data REGEXP '南方资本|万和|工银瑞投|瑞新ABS'
AND b.role_name IN (
    '1',
    '101',

    '2105270934093210001'
)
AND b.task_name = '线上审核'
AND (
    b.assignee_id = ''
    OR b.assignee_id = 'caiyitao'
)
AND b.del_status = 0
AND b. STATUS <> 3
ORDER BY
    urgent_flag DESC,
    b.add_time
LIMIT 0,
 10