需求:查询各个设备存在未消缺的缺陷,取缺陷等级最高的,作为这个设备当前的缺陷等级:
数据源:
select t.device_id, t.defect_level from sp_pd_defect t where t.deal_result <> 2 and t.device_id in ('03060000083602', '03060000085322', '03060000085762', '03060000087242', '03060003731354') group by t.device_id, t.defect_level order by t.device_id, t.defect_level asc;
如图:
要求: 需求获取红色部分的内容,,那么对应的SQL语句为
select device_id 设备ID, defect_level 缺陷等级 --缺陷等级,1紧急,2重大,3一般,4其他 from (select t.device_id, t.defect_level, row_number() over(partition by t.device_id order by t.defect_level asc) rn from sp_pd_defect t where t.deal_result <> 2 and t.device_id in ('03060000083602', '03060000085322', '03060000085762', '03060000087242', '03060003731354')) where rn = 1;
结果如图:
![](https://i-blog.csdnimg.cn/blog_migrate/5967846b568ba9b97ebc9c52576cd665.png)
其中: row_number() over(
partition
by 分组字段
order
by 排序字段
desc