meidu2021/daju/target/classes/com/xcgl/weixin/dao/WXDao_getDeviceStatusSumData.sql

SELECT
	count( DISTINCT monitor_point_id ) total,
	'离线' state 
FROM
	p_sensor_abnormal abn
	LEFT JOIN p_sensor_monitor_point point ON abn.monitor_point_id = point.id 
WHERE
	abn.state = '21' 
<#if projectid ?exists && projectid ?length gt 0>
	AND point.projectid = :projectid
</#if>
<#if qrydate ?exists && qrydate ?length gt 0>
	AND LEFT( abn.create_date, 10 ) = :qrydate
</#if>
UNION ALL
SELECT
	count( DISTINCT pointid ) total,
CASE
		qcdstate 
		WHEN '0' THEN
		'正常' 
		WHEN '11' THEN
		'超出上限' 
		WHEN '12' THEN
		'低于下限' 
	END AS state 
FROM
	p_sensor_record record 
WHERE 1=1
<#if qrydate ?exists && qrydate ?length gt 0>
	AND LEFT( recordtime, 10 ) = :qrydate 
</#if>
<#if projectid ?exists && projectid ?length gt 0>
	AND record.projectid = :projectid
</#if>
GROUP BY
	qcdstate