select d.tools_name, null as sum,null as num,dtp.specs,dtp.type,count( case when state =0 then 'state' end ) as 在库 ,
count(case when state =1 then 'state' end) 借出 ,
count(case when state =2 then 'state' end) 丢失,
count(case when state =3 then 'state' end) 报废
from dm_tools_KC d left join dm_tools_project dtp on d.tools_name = dtp.tools_name where d.TOOLS_NAME ='BH' group by d.tools_name,dtp.specs,dtp.type;
上面这个sql就是统计出不同工具名称的各个状态的数量,按工具名称进行分组统计