mysql

      SELECT
          b.uuid AS document_id,
          b.file_name,
          a.need_num,
          a.valid_num,
          d.scanning_file_id,
          d.scanning_flag,
          d.receive_num,
          d.come_from,
          d.from_person,
          d.memo,
          f.file_order,
               (
              CASE
              g.flag
              WHEN '0'
              THEN '未接收'
              WHEN NULL
              THEN '未接收'
              ELSE '已接收'
              END
              ) AS is_receive
              FROM
          biz_factoring_material_list a
              LEFT JOIN biz_document_define b ON a.document_id = b.uuid
              AND b.sys_status = 1
              LEFT JOIN biz_factoring_reception_temp c ON a.biz_id = c.biz_id
              AND c.sys_status = 1
              AND c.receive_file_type = 0
              LEFT JOIN biz_factoring_reception_detail_temp d ON c.uuid = d.receive_id
              AND a.document_id = d.document_id
              AND d.sys_status = 1
              LEFT JOIN biz_pay_comfirm_info e ON a.biz_id = e.uuid
              AND e.sys_status = 1
              LEFT JOIN biz_fullset_file_info f ON e.core_enterprise_id = f.core_enterprise_id
              AND e.financial_institutions_id = f.financial_institutions_id
              AND e.signing_body_id = f.signing_body_id
              AND a.document_id = f.file_id
              AND f.sys_status = 1
              LEFT JOIN (
          SELECT
           t.biz_id,
           t.document_id,
           MAX(t.flag) AS flag
          FROM (
           SELECT
               a.biz_id,
               a.document_id,
               b.approve_status,
               a.scanning_flag AS scanning_flag_0,
               b.scanning_flag AS scanning_flag_1,
               a.need_num,
               a.valid_num,
               b.receive_num,
               IF ( b.approve_status <> 31,
                   CASE WHEN ISNULL(b.receive_num) THEN
                           IF ( a.need_num - a.valid_num > 0, 0, 1 )
                       ELSE
                           IF ( a.need_num - a.valid_num - b.receive_num > 0, 0, 1 )
                   END,
                   IF ( a.need_num - a.valid_num > 0, 0, 1 )
               ) AS flag
           FROM
               biz_factoring_material_list a
           LEFT JOIN (
               SELECT
                   a.biz_id,
                   a.approve_status,
                   b.document_id,
                   b.receive_num,
                   b.scanning_flag
               FROM
                   biz_factoring_reception a
               INNER JOIN biz_factoring_reception_detail b ON a.uuid = b.receive_id
               AND b.sys_status = 1
               INNER JOIN bpm_process_instance d ON a.uuid = d.biz_uuid
               AND d.sys_status = 1
               AND d. STATUS <> '已终止'
               AND d. STATUS <> '已挂起'
               WHERE
                   a.sys_status = 1
               AND a.receive_file_type = 0
               AND a.biz_id = '202edc33ef1f46e794d132864fdb3399'
           ) b ON a.biz_id = b.biz_id
           AND a.document_id = b.document_id
           LEFT JOIN biz_document_define c ON a.document_id = c.uuid
           AND c.sys_status = 1
           WHERE a.sys_status = 1
           AND a.receive_flag = 1
           AND c.file_source = 0
           AND a.biz_id = '202edc33ef1f46e794d132864fdb3399'
          ) t GROUP BY t.biz_id, t.document_id
              ) g ON a.biz_id = g.biz_id
              AND a.document_id = g.document_id
              WHERE
          a.sys_status = 1
              AND a.receive_flag = 1
              AND b.file_source = 0
              AND f.file_type = 04
              AND a.biz_id = '202edc33ef1f46e794d132864fdb3399'
              ORDER BY
          f.file_order ASC ;