统计状态数目

 

SELECT arch.dept_id ,dept.dept_name,COUNT(*) AS arch_sum, SUM(CASE arch_status WHEN 0 THEN 1 ELSE 0 END) in_stock,SUM(CASE arch_status WHEN 1 THEN 1 ELSE 0 END) on_loan,SUM(CASE arch_status WHEN 2 THEN 1 ELSE 0 END) in_lose,SUM(CASE arch_status WHEN 3 THEN 1 ELSE 0 END) to_be_expire,SUM(CASE arch_status WHEN 4 THEN 1 ELSE 0 END) in_scrap FROM T_ARCH arch LEFT JOIN DB_ZJMF.T_ORG_DEPT dept  ON arch.dept_id = dept.idWHERE arch.is_delete = 0GROUP BY arch.dept_id