SELECT
bi.article_no,  -- 存货档案
bi.ref_report_no,  -- 参照报告号码
bi.u8_batch_no,  -- U8批次号码，仓库内部使用
bi.report_batch_no, -- 证书批次号码，对外用这个
bi.u8_ind_po_id,  -- PO_PODetails.ID
case bi.receive_time when 0 then null else FROM_UNIXTIME(bi.receive_time, "%Y-%m-%d") end `receive_time`, -- 收样日期
case bi.report_date when 0 then null else FROM_UNIXTIME(bi.report_date, "%Y-%m-%d") end `report_date`, -- 证书日期
case bi.report_due_date when 0 then null else FROM_UNIXTIME(bi.report_due_date, "%Y-%m-%d") end `report_due_date`,

bi.conc,
case WHEN bi.conc='1' then '合格' when bi.conc='2' then '不合格' when bi.conc='3' then '数据' when bi.conc='4' then '特准合格' when bi.conc='5' then '无结论' when bi.conc='6' then '不适用' when bi.conc='7' then'不合格-已审' END "状态",
bi.attr,
bi.report_status,
case tmp2.plan_time when 0 then null else FROM_UNIXTIME(tmp2.plan_time, "%Y-%m-%d") end `plan_time`,
case bi.prepare_time when 0 then null else FROM_UNIXTIME(bi.prepare_time, "%Y-%m-%d") end `prepare_time`,
case bi.data_time when 0 then null else FROM_UNIXTIME(bi.data_time, "%Y-%m-%d") end `data_time`,
case bi.check_time when 0 then null else FROM_UNIXTIME(bi.check_time, "%Y-%m-%d") end `check_time`,

case bi.report_time when 0 then null else FROM_UNIXTIME(bi.report_time, "%Y-%m-%d") end `report_time`,
case bi.approve_time when 0 then null else FROM_UNIXTIME(bi.approve_time, "%Y-%m-%d") end `approve_time`,
bi.cas_no,
bi.factory_batch_no,
case bi.update_time when 0 then null else FROM_UNIXTIME(bi.update_time, "%Y-%m-%d") end `update_time`,
tmp2.id,
case bi.add_time when 0 then null else FROM_UNIXTIME(tmp2.add_time, "%Y-%m-%d") end `add_time`,
tmp2.test_id,
tmp2.weight,
tmp3.title

FROM
bv_report_index bi

LEFT join (
select
bt.id,
bt.report_no,
bt.plan_time,
bt.receive_time,
bt.prepare_time,
bt.data_time,
bt.check_time,
bt.add_time,
bt.update_time,
bt.weight,
bt.test_id

FROM
bv_report_test bt
GROUP BY(bt.report_no)
)tmp2
on bi.report_no=tmp2.report_no
LEFT JOIN(
SELECT id, title FROM bv_test
)tmp3
on tmp3.id=tmp2.test_id