mysql 窗体函数

mysql 窗体函数排序

应用于一对多，获取多条记录中最新的一条

本例子用于一个设备拥有多条抄表记录，获取最新的一条记录的数据

例：

SELECT
	* 
FROM
	(
	SELECT
		build.NAME '楼栋',
		dorm.NAME AS '寝室',
		d.device_sn '设备',
	CASE
			d.biz 
			WHEN 82 THEN
			'水' 
			WHEN 83 THEN
			'电' 
			WHEN 84 THEN
			'气' ELSE '其他' 
		END '类型',
	( SELECT sum( quantity ) FROM orders WHERE d.device_sn = device_sn AND `status` = 5 ) AS recharge_number,
	record.base_recharge,
	record.base_use,
	record.activity_total,
	record.activity_value,
	( row_number ( ) over ( PARTITION BY record.device_sn ORDER BY record.id DESC ) ) AS ss 
FROM
	device_bind d
	LEFT JOIN school_building build ON d.building_id = build.id
	LEFT JOIN school_dorm dorm ON d.dorm_id = dorm.id
	LEFT JOIN device_record record ON d.device_sn = record.device_sn 
WHERE
	d.school_id = 125 
ORDER BY
	dorm.NAME + '',
	d.biz 
	) AS a where ss=1

结果：