SELECT
bv_report_index.report_no AS report_no,
tmp.material_article_no,
coalesce(bv_report_index.report_batch_no, bv_report_index.u8_batch_no) AS batch_no,
bv_report_index.path AS path,
bv_report_index.purity AS purity
FROM bv_report_index
INNER JOIN (
    SELECT bv_report_index.product_id AS product_id,
        bv_product.material_article_no,
    coalesce(bv_report_index.report_batch_no, bv_report_index.u8_batch_no) AS cert_batch_no,
    max(bv_report_index.add_time) AS add_time
    FROM bv_report_index
    LEFT OUTER JOIN bv_product
    ON bv_product.id = bv_report_index.product_id
WHERE (bv_product.material_article_no LIKE concat('%%', 'C4X-1108%'))
-- and coalesce(bv_report_index.report_batch_no, bv_report_index.u8_batch_no) = '0602-RD-0006'
AND bv_report_index.approve_time != 0
AND bv_report_index.ref_report_no = 0
GROUP BY coalesce(bv_report_index.report_batch_no, bv_report_index.u8_batch_no)
) AS tmp
ON tmp.cert_batch_no = coalesce(bv_report_index.report_batch_no, bv_report_index.u8_batch_no)
AND tmp.add_time = bv_report_index.add_time
AND tmp.product_id = bv_report_index.product_id