查询满足条件的最新数据（逐步优化，mysql、达梦数据库）

• 2020 年 12 月 8 日
• 筆記
• 达梦数据库

1.条件：报警信息表sensor_warning

2.需求：

查询当前车厢的、不同设备的、所有处理未完成的、不同报警原因的、时间最新的数据集合，最后按设备id或报警时间排序

3.原始sql，不满足实际情况

SELECT
	a.id,
	a.sensor_type,
	a.device_status,
	a.train_no,
	a.device_id,
	a.sensor_no,
	a.carriage_no,
	a.process_progress,
	a.handle_person,
	a.create_time,
	a.position,
	a.update_time,
	a.reason 
FROM
	sensor_warning AS a
	JOIN (
SELECT
	max( b.device_id ) AS device_id,
	max( b.create_time ) AS create_time 
FROM
	sensor_warning AS b 
WHERE
	b.process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND b.carriage_no = 'CARID-008' 
GROUP BY
	b.device_id 
	) AS m ON a.device_id = m.device_id 
	AND a.create_time = m.create_time 
ORDER BY
	a.device_id

4.为什么不满足实际情况？

上面的sql只能查出当前车厢的、不同设备的、所有处理未完成的、时间最新的数据集合。并没有根据reason分类。
而且这里reason字段中的数据还比较特殊，例举reason字段中的个别报警原因就知道了：

转向架振动超阈值:1.23m/s²;
转向架振动超阈值:0.48m/s²
车体振动超阈值:-0.24m/s²
车体振动超阈值:-0.55m/s²

我们可以看到reason中存在数值，如果按照上面的sql查询，那么上述的4条数据都会被查出来，而实际上只有两条数据才是我们想要的结果。

5.mysql解决方案

使用字符串截取函数SUBSTRING_INDEX()，按照冒号截取，因为数据中有中文冒号和英文冒号所以这里做了两次截取。

SELECT
	a.device_id,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.reason, ':', 1 ), '：', 1 ) reason,
	max(a.create_time) AS create_time 
FROM
	(
SELECT
	id,
	sensor_type,
	device_status,
	train_no,
	device_id,
	sensor_no,
	carriage_no,
	process_progress,
	handle_person,
	create_time,
	position,
	update_time,
	reason 
FROM
	sensor_warning
WHERE
	process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND carriage_no = 'CARID-008' 
ORDER BY
	create_time DESC 
	) a 
GROUP BY
	a.device_id,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.reason, ':', 1 ), '：', 1 ) 
	
	ORDER BY a.device_id

6.达梦数据库解决方案

（ps：同orcale）
然而达梦数据库中并不支持SUBSTRING_INDEX()函数，达梦这里使用substr()函数。

substr函数格式 (字符截取函数)

　　格式1： substr(string string, int a, int b);

　　格式2：substr(string string, int a) ;

解析：

    格式1：
        1、string 需要截取的字符串
        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
        3、b 要截取的字符串的长度

    格式2：
        1、string 需要截取的字符串
        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

6.1.达梦数据库解决方案1

SELECT
	a.id,
	a.device_id,
	a.create_time,
	a.reason 
FROM
	sensor_warning AS a
	JOIN (
SELECT
	b.device_id device_id,
	substr( b.reason, 1, 7 ) reason,
	max( b.id ) AS id,
	max( b.create_time ) AS create_time 
FROM
	sensor_warning AS b 
WHERE
	process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND carriage_no = 'CARID-008' 
GROUP BY
	b.device_id,
	substr( b.reason, 1, 7 ) 
	) AS m ON a.id = m.id 
ORDER BY
	a.create_time

6.2.达梦数据库解决方案2

SELECT
	a.device_id,
	substr( a.reason, 1, 7 ) reason,
	max( a.create_time ) AS create_time 
FROM
	(
SELECT
	id,
	device_id,
	carriage_no,
	create_time,
	reason 
FROM
	sensor_warning 
WHERE
	process_progress IN ( '0', '1' ) 
	AND warning_type = 0 
	AND carriage_no = 'CARID-008' 
	) a 
GROUP BY
	a.device_id,
	substr( a.reason, 1, 7 ) 
ORDER BY
	a.create_time