SELECT b.maiorgname AS maiorgname,COUNT(*) AS number,t.numbersum
FROM se_ft_basic_3000 AS b INNER JOIN se_dt_sbzl_dl AS d INNER JOIN se_dt_xzqh_cmn AS c INNER JOIN
(SELECT b.maiorgname AS maiorgname,COUNT(*) AS numbersum
FROM se_ft_basic_3000 AS b INNER JOIN se_dt_sbzl_dl AS d INNER JOIN se_dt_xzqh_cmn AS c
ON b.equupscode=d.equupscode AND b.dm_ds=c.DM_DS AND b.dm_qx=c.DM_QX
WHERE IF("设备所在地州市"='',1,b.dm_ds=(SELECT DM_DS FROM se_dt_xzqh_cmn WHERE MC="设备所在地州市"))
AND IF("设备所在区县"='',1,b.dm_qx=(SELECT DM_QX FROM se_dt_xzqh_cmn WHERE MC="设备所在区县")) 
AND IF("首次检验日期开始"='',1,b.insdate>"首次检验日期开始" AND b.insdate<"首次检验日期结束")
AND IF("设备检验类别描述"='',1,b.testsort=(SELECT testsort FROM se_dt_testsort WHERE comments="设备检验类别描述"))
AND IF("所属设备大类名称"='',1,LEFT(b.equupscode,1)=LEFT((SELECT equupscode FROM se_dt_sbzl_dl WHERE equipname="所属设备大类名称"),1))
GROUP BY b.useorgname) AS t
ON b.equupscode=d.equupscode AND b.dm_ds=c.DM_DS AND b.dm_qx=c.DM_QX AND b.maiorgname=t.maiorgname
WHERE IF("设备所在地州市"='',1,b.dm_ds=(SELECT DM_DS FROM se_dt_xzqh_cmn WHERE MC="设备所在地州市"))
AND IF("设备所在区县"='',1,b.dm_qx=(SELECT DM_QX FROM se_dt_xzqh_cmn WHERE MC="设备所在区县")) 
AND IF("首次检验日期开始"='',1,b.insdate>"首次检验日期开始" AND b.insdate<"首次检验日期结束")
AND IF("设备检验类别描述"='',1,b.testsort=(SELECT testsort FROM se_dt_testsort WHERE comments="设备检验类别描述"))
AND IF("所属设备大类名称"='',1,LEFT(b.equupscode,1)=LEFT((SELECT equupscode FROM se_dt_sbzl_dl WHERE equipname="所属设备大类名称"),1))
AND insresult="不合格"
GROUP BY b.useorgname;