SELECT a.uuid,a.pay_id, CONCAT( ‘付款确认书编号:‘, a.pay_id, ‘ ‘, d.item_company_name, ‘对‘, b.suppliers_name, ‘之合同‘, ( CASE WHEN e.financial_type = 1 THEN CASE WHEN ( ISNULL(c.contract_no) OR c.contract_no = ‘‘ ) THEN CONCAT( ‘名称:‘, REPLACE(c.contract_name, ‘&‘, ‘&‘) ) ELSE CONCAT( ‘编号:‘, REPLACE(c.contract_no, ‘&‘, ‘&‘) ) END ELSE CASE WHEN ( ISNULL(a.base_contract_no) OR a.base_contract_no = ‘‘ ) THEN CONCAT( ‘名称:‘, REPLACE(a.base_contract_name, ‘&‘, ‘&‘) ) WHEN ( ISNULL(a.base_contract_name) OR a.base_contract_name = ‘‘ ) THEN CONCAT( ‘编号:‘, REPLACE(a.base_contract_no, ‘&‘, ‘&‘) ) ELSE CONCAT( ‘编号:‘, REPLACE(a.base_contract_no, ‘&‘, ‘&‘), ‘,‘, ‘合同名称:‘, REPLACE(a.base_contract_name, ‘&‘, ‘&‘) ) END END ), ‘,发票号:‘, a.invoice_description, ‘,转让金额:‘, ROUND(a.account_receivable, 2), IF( a.financial_institutions_id = ‘f1018‘, ‘元,具体发票金额及转让金额详见附件‘, ‘元‘ ) ) AS description FROM biz_pay_comfirm_info a, biz_suppliers b, biz_contract_info c, biz_item_company d, biz_financial_institutions e , biz_zd_unified_register_detail f WHERE a.suppliers_id = b.uuid AND a.contract_id = c.uuid AND a.item_company_id = d.uuid AND a.financial_institutions_id = e.uuid AND a.sys_status = 1 AND b.sys_status = 1 AND c.sys_status = 1 AND d.sys_status = 1 AND e.sys_status = 1 AND f.sys_status = 1 AND a.uuid=f.pay_id AND a.uuid NOT IN ( SELECT pay_id FROM biz_rollback_pay WHERE sys_status = 1 AND biz_state <> 15 ) AND f.reception_id=‘996075f86c774ec1ba52470f2f6cbbfe‘
原文:https://www.cnblogs.com/xiaowoniulx/p/10840084.html