ID_, BELONG_AREA_ID_, BELONG_AREA_CODE_, BELONG_AREA_NAME_, ROAD_ID_, ROAD_NAME_, ROAD_CODE_, WARNING_TIME_, RELIEVE_WARNING_TIME_, WARNING_TYPE, LNG_, LAT_, LOCATION_STATION_NUMBER_, WARNING_LEVEL_, PUBLISH_DEVICE_CHANNEL_, PUBLISH_DEVICE_NAME_, PUBLISH_DEVICE_TYPE_, PUBLISH_DEVICE_UNIT_, CREATE_BY_, CREATE_ORG_ID_, CREATE_ORG_NAME_, CREATE_COMPANY_ID_, CREATE_COMPANY_NAME_, CREATE_NAME_, CREATE_TIME_, UPDATE_BY_, UPDATE_NAME_, UPDATE_COMPANY_NAME_, UPDATE_COMPANY_ID_, UPDATE_ORG_ID_, UPDATE_ORG_NAME_, UPDATE_TIME_, LAST_TIME_, IS_DELE_, WARN_RANGE_, PUB_STATUS_, START_PEG_VAL_, END_PEG_VAL_, RESPONSE_MECHANISM_, CONTROL_THRESHOLD_, WATER_CONTENT_, TAKE_ACTION_, CONTROL_SITUATION_, PUBLISH_SITUATION_, PEOPLE_SITUATION_, CAR_SITUATION_, INSPECT_MILEAGE_, PRESETS_TEAM_SITUATION_, PRESETS_PEOPLE_SITUATION_, PRESETS_CAR_SITUATION_, PRESETS_GOODS_SITUATION_ SELECT r.id_, r.name_, COALESCE(car_counts.car_count, 0) AS presetsCarSituation, COALESCE(team_counts.team_count, 0) AS presetsTeamSituation, COALESCE(member_counts.member_count, 0) AS presetsPeopleSituation, COALESCE(xj.xjlc, 0) AS inspectMileage, COALESCE(xj.xjrs, 0) AS peopleSituation, COALESCE(xj.xjcs, 0) AS carSituation FROM biz_base_road r LEFT JOIN ( SELECT ROAD_ID_, COUNT(*) AS car_count FROM biz_base_car WHERE is_dele_ = '0' GROUP BY ROAD_ID_ ) car_counts ON r.ID_ = car_counts.ROAD_ID_ LEFT JOIN ( SELECT ROAD_ID_, COUNT(*) AS team_count FROM biz_base_team WHERE is_dele_ = '0' GROUP BY ROAD_ID_ ) team_counts ON r.ID_ = team_counts.ROAD_ID_ LEFT JOIN ( SELECT ROAD_ID_, COUNT(*) AS member_count FROM biz_base_team_member WHERE is_dele_ = '0' GROUP BY ROAD_ID_ ) member_counts ON r.ID_ = member_counts.ROAD_ID_ LEFT JOIN ( SELECT road_id_, SUM(COALESCE(NULLIF(mileage_, 0), 0)) AS xjlc, SUM( CASE WHEN INSPECT_USERS_NAME_ IS NULL OR INSPECT_USERS_NAME_ = '' THEN 0 ELSE LENGTH(INSPECT_USERS_NAME_) - LENGTH(REPLACE(INSPECT_USERS_NAME_, ',', '')) + 1 END ) AS xjrs, COUNT(DISTINCT CAR_CODE_) AS xjcs FROM biz_inspection_result WHERE DATE(START_TIME_) = CURDATE() GROUP BY road_id_ ) xj ON r.ID_ = xj.ROAD_ID_ WHERE r.id_=#{roadId}