一层一层下钻

 WITH recursive
            dept_tree AS (
                SELECT m.MineID        mine_id,
                       sd.id        AS dept_id,
                       sd.dept_name AS topName,
                       sd.id        AS topId
                FROM jmis_dept sd
                         left join k_mine m on sd.id = m.dept_id
                WHERE sd.parent_id = #{deptId}
                UNION ALL
                SELECT m.MineID    AS mine_id,
                       sd1.id      AS dept_id,
                       pt2.topName AS topName,
                       pt2.topId   AS topId
                FROM jmis_dept sd1
                         left join k_mine m on sd1.id = m.dept_id
                         JOIN dept_tree pt2 ON sd1.parent_id = pt2.dept_id
            ),
            minesubsysupdate as (SELECT u.MineID,
                                        case
                                            WHEN ms.`CommUpdateTimeW` IS NULL THEN 'null'
                                            when timestampdiff(SECOND, `ms`.`CommUpdateTimeW`,
                                                                       sysdate() - `ms`.`MaxDelayTime`) > 0
                                                then 'online'
                                            else 'timeout' end as type
                                 FROM `k_mine` `u`
                                          LEFT JOIN `k_minesubsysupdate` `ms` ON
                                     `u`.`MineID` = `ms`.`MineID`

                                 WHERE `u`.`IsValid` = 1)

        SELECT count(mine_id) count,
				if(sum(if(type='null',1,0)) is null ,0,sum(if(type='null',1,0)))as nullSum,
				if(sum(if(type='online',1,0)) is null ,0,sum(if(type='online',1,0)))as onlineSum,
				if(sum(if(type='timeout',1,0)) is null ,0,sum(if(type='timeout',1,0)))as timeoutSum,
        topId deptId,
        topName deptName
        FROM
            dept_tree d left join minesubsysupdate sub
        on d.mine_id=sub.MineID
        group by topId