Pandaaaa906

Untitled

Dec 8th, 2021 (edited)
897
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2. bi.article_no,  -- 存货档案
  3. bi.ref_report_no,  -- 参照报告号码
  4. bi.u8_batch_no,  -- U8批次号码,仓库内部使用
  5. bi.report_batch_no, -- 证书批次号码,对外用这个
  6. bi.u8_ind_po_id,  -- PO_PODetails.ID
  7. case bi.receive_time when 0 then null else FROM_UNIXTIME(bi.receive_time, "%Y-%m-%d") end `receive_time`, -- 收样日期
  8. case bi.report_date when 0 then null else FROM_UNIXTIME(bi.report_date, "%Y-%m-%d") end `report_date`, -- 证书日期
  9. case bi.report_due_date when 0 then null else FROM_UNIXTIME(bi.report_due_date, "%Y-%m-%d") end `report_due_date`,
  10.  
  11. bi.conc,
  12. 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 "状态",
  13. bi.attr,
  14. bi.report_status,
  15. case tmp2.plan_time when 0 then null else FROM_UNIXTIME(tmp2.plan_time, "%Y-%m-%d") end `plan_time`,
  16. case bi.prepare_time when 0 then null else FROM_UNIXTIME(bi.prepare_time, "%Y-%m-%d") end `prepare_time`,
  17. case bi.data_time when 0 then null else FROM_UNIXTIME(bi.data_time, "%Y-%m-%d") end `data_time`,
  18. case bi.check_time when 0 then null else FROM_UNIXTIME(bi.check_time, "%Y-%m-%d") end `check_time`,
  19.  
  20. case bi.report_time when 0 then null else FROM_UNIXTIME(bi.report_time, "%Y-%m-%d") end `report_time`,
  21. case bi.approve_time when 0 then null else FROM_UNIXTIME(bi.approve_time, "%Y-%m-%d") end `approve_time`,
  22. bi.cas_no,
  23. bi.factory_batch_no,
  24. case bi.update_time when 0 then null else FROM_UNIXTIME(bi.update_time, "%Y-%m-%d") end `update_time`,
  25. tmp2.id,
  26. case bi.add_time when 0 then null else FROM_UNIXTIME(tmp2.add_time, "%Y-%m-%d") end `add_time`,
  27. tmp2.test_id,
  28. tmp2.weight,
  29. tmp3.title
  30.  
  31. FROM
  32. bv_report_index bi
  33.  
  34. LEFT join (
  35. select
  36. bt.id,
  37. bt.report_no,
  38. bt.plan_time,
  39. bt.receive_time,
  40. bt.prepare_time,
  41. bt.data_time,
  42. bt.check_time,
  43. bt.add_time,
  44. bt.update_time,
  45. bt.weight,
  46. bt.test_id
  47.  
  48. FROM
  49. bv_report_test bt
  50. GROUP BY(bt.report_no)
  51. )tmp2
  52. on bi.report_no=tmp2.report_no
  53. LEFT JOIN(
  54. SELECT id, title FROM bv_test
  55. )tmp3
  56. on tmp3.id=tmp2.test_id
RAW Paste Data