得到 ROW_NUMBER() 函数使用详解
row_number() over(partition by 字段1 order by 字段2 )按over里的partition by 字段1进行分组,然后按Over里的order by字段2进行组内排序,每组从1开始
这里partition by 分组与group by不同在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录
实例如下:
需求场景(取最新的结果信息):检验日期 ,工序,工单,规格型号 ,抽检数量,不良数量, 抽检结果
正常情况下:
select 检验日期 ,工序,工单,规格型号 ,SUM(抽检数量),SUM(不良数量), 抽检结果 from dual
group by
检验日期 ,工序,工单,规格型号 ,抽检结果
得到的结果是:每个工序,工单,规格型号汇总出来的数量 对应多个检验结果
使用ROW_NUMBER()的情况下:
-- 取最新一笔的备注信息
WITH NEW_MSG(工序,工单,规格型号,结果 ) AS (
SELECT 工序,工单,规格型号,结果 FROM (
SELECT
工序
,工单
,规格型号
,结果
,row_number() OVER ( PARTITION BY 工序,工单,规格型号 order by 检验日期 desc) AS row
FROM A
)WHERE row=1
)
SELECT
A.检验日期
,A.工序
,A.工单
,A.规格型号
,msg.结果
,sum(抽检数量)
,sum(不良数量)
FROM A.
LEFT JOIN NEW_MSG msg ON msg.工序 = A.工序
AND msg.工单 = A.工单
AND msg.规格型号 = A.规格型号
group by
A.检验日期
,A.工序
,A.工单
,A.规格型号
,msg.结果
得到的结果是:每个工序,工单,规格型号 ,对应最新检验结果