SELECT

CASE WHEN

t1.areaCode THEN

( SELECT sc.areaName FROM s_citycounty sc WHERE sc.areaCode = t1.areaCode ) 

END AS areaName,

t1.countall,

t2.zcall,

t2.anjuan,

t3.lhzctall,

t3.xzchjds,

t3.czjcd,

t1.penalty AS penalty1,

t2.penalty AS penalty2,

t3.penalty AS penalty3 

FROM

(

SELECT

substr( bb.areaCode, 1, 4 ) AS areaCode,

count( bci.id ) AS countall,

sum( bb.penalty ) AS penalty 

FROM

s_project sp

LEFT JOIN bwsn_count_integrity bci ON sp.id = bci.pid

LEFT JOIN bwsn_basicinfo bb ON sp.id = bb.pid 

WHERE

bb.ishistory = 1 

AND bb.historytype = "案件上传" 

AND sp.STATUS = "结案" 

AND DATE_FORMAT(sp.createTime,'%Y-%m-%d') >="${dateEnd_c_c}"

AND DATE_FORMAT(sp.createTime,'%Y-%m-%d') <="${dateStart_c_c}"

AND DATE_FORMAT(sp.endTime,'%Y-%m-%d')>="${dateEnd_c_c_c}"

AND DATE_FORMAT(sp.endTime,'%Y-%m-%d')<="${dateStart_c_c_c}"

GROUP BY

substr( bb.areaCode, 1, 4 ) 

) t1

LEFT JOIN (

SELECT

substr( bb.areaCode, 1, 4 ) AS areaCode,

count( bci.id ) AS zcall,

sum( bci.num & 1 = 1 ) AS anjuan,

sum( bb.penalty ) AS penalty 

FROM

s_project sp

LEFT JOIN bwsn_count_integrity bci ON sp.id = bci.pid

LEFT JOIN bwsn_basicinfo bb ON sp.id = bb.pid 

WHERE

bb.ishistory = 1 

AND bb.historytype = "案件上传" 

AND bb.caseType = "治超" 

AND sp.STATUS = "结案" 

AND DATE_FORMAT(sp.createTime,'%Y-%m-%d') >="${dateEnd_c_c}"

AND DATE_FORMAT(sp.createTime,'%Y-%m-%d') <="${dateStart_c_c}"

AND DATE_FORMAT(sp.endTime,'%Y-%m-%d')>="${dateEnd_c_c_c}"

AND DATE_FORMAT(sp.endTime,'%Y-%m-%d')<="${dateStart_c_c_c}"

GROUP BY

substr( bb.areaCode, 1, 4 ) 

) t2 ON t1.areaCode = t2.areaCode

LEFT JOIN (

SELECT

substr( bb.areaCode, 1, 4 ) AS areaCode,

count( bci.id ) AS lhzctall,

sum( bci.num & 2 = 2 ) AS xzchjds,

sum( bci.num & 4 = 4 ) AS czjcd,

sum( bb.penalty ) AS penalty 

FROM

s_project sp

LEFT JOIN bwsn_count_integrity bci ON sp.id = bci.pid

LEFT JOIN bwsn_basicinfo bb ON sp.id = bb.pid 

WHERE

bb.ishistory = 1 

AND bb.historytype = "案件上传" 

AND bb.caseType = "联合治超" 

AND sp.STATUS = "结案" 

AND DATE_FORMAT(sp.createTime,'%Y-%m-%d') >="${dateEnd_c_c}"

AND DATE_FORMAT(sp.createTime,'%Y-%m-%d') <="${dateStart_c_c}"

AND DATE_FORMAT(sp.endTime,'%Y-%m-%d')>="${dateEnd_c_c_c}"

AND DATE_FORMAT(sp.endTime,'%Y-%m-%d')<="${dateStart_c_c_c}"

GROUP BY

substr( bb.areaCode, 1, 4 ) 

) t3 ON t1.areaCode = t3.areaCode