id_, device_code_,device_type_, device_status_, start_time_, end_time_, time_length_, last_time_ select * from ( select log.*, d.name_ deviceName from DEVICE_STATUS_LOG log inner join DEVICE_INFO d on d.code_ = log.device_code_ and d.IS_DELE_ = '0' union all select log.* , d.name_ deviceName from DEVICE_STATUS_LOG log inner join device_video_point d on d.code_ = log.device_code_ and d.IS_DELE_ = '0' ) tab ${ew.customSqlSegment} select log.device_status_ status , date_format(log.start_time_, '%Y-%m') time , date_format(log.start_time_, '%Y-%m-%d') time from DEVICE_STATUS_LOG log where device_status_ is not null and device_type_ is not null and DATE_FORMAT(start_time_, #{dto.dateType}) = #{dto.time} DATE_FORMAT(now(), #{dto.dateType}) and log.device_status_ = #{dto.deviceStatus} select * from ( SELECT d.id_, d.name_ AS deviceName, d.code_ AS deviceCode, ifnull(l.timeLength, 0) AS timeLength, ifnull(l1.timeLength, 0) AS timeLength1, t.name_ as deviceType, t.code_ as deviceTypeCode FROM device_info d LEFT JOIN ( SELECT device_code_, sum(time_length_) as timeLength FROM device_status_log WHERE device_status_ = '1' GROUP BY device_code_) l on l.device_code_ = d.code_ LEFT JOIN ( SELECT device_code_, sum(time_length_) as timeLength FROM device_status_log WHERE device_status_ = '2' GROUP BY device_code_) l1 on l1.device_code_ = d.code_ LEFT JOIN device_product_info p on p.id_ = d.product_id_ LEFT JOIN device_product_type t on t.id_ = p.type_id_ WHERE d.is_dele_ = '0' and p.is_dele_ = '0' and t.is_dele_ = '0' union all SELECT d.id_, d.name_ AS deviceName, d.code_ AS deviceCode, ifnull(l.timeLength, 0) AS timeLength, ifnull(l1.timeLength, 0) AS timeLength1, '摄像头' as deviceType, 'video' as deviceTypeCode FROM device_video_point d LEFT JOIN ( SELECT device_code_, sum(time_length_) as timeLength FROM device_status_log WHERE device_status_ = '1' GROUP BY device_code_) l on l.device_code_ = d.code_ LEFT JOIN ( SELECT device_code_, sum(time_length_) as timeLength FROM device_status_log WHERE device_status_ = '2' GROUP BY device_code_) l1 on l1.device_code_ = d.code_ ) tab WHERE 1=1 and tab.deviceName like concat('%',#{ew.name},'%') and tab.deviceCode like concat('%',#{ew.code},'%') and tab.deviceTypeCode = #{ew.deviceType} select count(1) count, DATE_FORMAT(start_time_,'%Y-%m') month from device_status_log log where device_status_ = '2' and start_time_ >= DATE_SUB(start_time_, INTERVAL 12 MONTH) group by month select count(1) count, ifnull(dba.name_, '') area from device_status_log sta left join device_info di on di.code_ = sta.device_code_ left join device_base_area dba on dba.id_ = di.area_id_ where sta.device_status_ = '2' and sta.start_time_now() and dba.name_ is not null group by dba.name_ SELECT count(1) count, dpt.name_ type FROM device_status_log log LEFT JOIN device_info di ON di.code_ = log.device_code_ LEFT JOIN device_product_info pi ON pi.id_ = di.product_id_ LEFT JOIN device_product_type dpt ON dpt.id_ = pi.type_id_ WHERE pi.device_type_ IS NOT NULL AND device_status_ = #{dto.deviceStatus} AND DATE_FORMAT( log.start_time_, #{dto.dateType} ) = #{dto.time} GROUP BY type