sql：
SELECT
d.dept_id AS deptId ,
dept_name AS deptName ,
GROUP_CONCAT(DISTINCT CASE WHEN sd.declare_status > 2 and sa.assess_year = ? THEN sd.declare_unit END) AS companyNames,
SUM(IF(sd.declare_status > 2
and sa.assess_year = ?, 1, 0)) AS declareNum,
SUM(IF(sd.declare_status > 2
AND sd.area_is_recommend = 1
and sa.assess_year = ?, 1, 0)) AS declareRecommendNum,
0 AS operateNum ,
0 AS operateRecommendNum,
0 AS suggestNum ,
0 AS suggestRecommendNum,
SUM(IF(sd.declare_status > 2
and sa.assess_year = ?, 1, 0)) AS countNum,
SUM(IF(sd.declare_status > 2
AND sd.area_is_recommend = 1
and sa.assess_year = ?, 1, 0)) AS recommendNum
FROM
sys_dept d
LEFT JOIN ass_science_declare sd
ON
d.dept_id = sd.dept_id
and sd.is_del = 0
LEFT JOIN ass_science_assess sa
ON
sa.id = sd.assess_id
and sa.is_del = 0
WHERE
d.del_flag = 0
AND d.status = 0
AND d.is_direct = 1
GROUP BY
d.dept_id
ORDER BY
d.dept_id ASC;