meidu2021/daju/target/classes/com/xcgl/weixin/sql/WXDao_getSensorLiveData.sql

SELECT
	deviceid,
	devicecode,
	devicename,
	pointid,
	pointcode,
	pointname,
	VALUE,
	left(recordtime,19) recordtime,
	qcdstate ,
	CONCAT(IfNULL(floor,''),'~',IfNULL(upper,'')) normalvalue,
	unit
FROM
	(
	SELECT
		record.deviceid AS deviceid,
		record.devicename AS devicename,
		de.CODE AS devicecode,
		pointid,
		pointcode,
		pointname,
		param_value AS VALUE ,
		recordtime,
		record.qcdstate AS qcdstate ,
		record.UPPER upper ,
		record.floor floor,
		record.typeunit unit
	FROM
		p_sensor_record record
		LEFT JOIN p_device de ON record.deviceid = de.id 
	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>
	ORDER BY
		recordtime DESC 
	) tmp 
GROUP BY
	pointid UNION ALL
SELECT
	deviceid,
	devicecode,
	devicename,
	pointid,
	pointcode,
	pointname,
	VALUE,
	recordtime,
	qcdstate ,
	'' as  normalvalue,
	'' as unit
FROM
	(
	SELECT
		point.deviceid AS deviceid,
		dev.NAME AS devicename,
		dev.CODE AS devicecode,
		monitor_point_id AS pointid,
		point.CODE AS pointcode,
		point.NAME AS pointname,
		'' AS VALUE,
		abn.create_date AS recordtime,
		abn.state AS qcdstate 
	FROM
		p_sensor_abnormal abn
		LEFT JOIN p_sensor_monitor_point point ON abn.monitor_point_id = point.id
		LEFT JOIN p_device dev ON point.deviceid = dev.id 
	WHERE
		abn.state = '21' 
		<#if projectid ?exists && projectid ?length gt 0>
		AND dev.projectid = :projectid
		</#if>
		AND LEFT ( abn.create_date, 10 ) = :qrydate 
	ORDER BY
		abn.create_date DESC 
	) tmp 
GROUP BY
	pointid