分组排序:ROW_NUMBER() OVER (PARTITION BY) 用例解析

最新推荐文章于 2025-03-01 00:36:07 发布

m0_70868551

最新推荐文章于 2025-03-01 00:36:07 发布

阅读量2.4k

点赞数 13

文章标签：数据结构

本文链接：https://blog.csdn.net/m0_70868551/article/details/140105524

版权

1.用例内容

通过一个容易理解,erp系统中较为常见的业务场景,使用分组排序解决业务问题,来说明分组排序函数的使用方法和逻辑场景.

2.应用场景

普通的order by 排序,是整个查询的排序,只能有一个,如果要对分组后,表中的每组数据进行排序,则不能满足,此时就需要用到分组排序,将数据按一个字段或多个字段进行分组,但不对数据进行聚合(sum,avg,count等),而是对每组数据进行排序从而生成一个排序号,具体方式为在子查询中通过分组一个字段作为排序号;

3.语法格式

[排序号名称 = ROW_NUMBER()  OVER (partition by [分组的字段1],[分组的字段n] order by [排序字段])

4.用例解析

业务场景:
生产日报表中记录每个班次生产对应的订单号,以及每个班次生产的耗用材料信息,现在要将这些信息统计到结单表中,也就是每个订单号一条数据,这条数据包含各工序总耗用,但是由于订单生产中可能用到多种材料,所以材料名称和规格,只取第一条这个订单第一条生产日报数据中的名称和规格,但是耗用取该订单对应工序的耗用综合;这里需按照订单和工序进行分组,取第一条,也就用到了分组排序;

建表语句

create table test_produce (--生产日报
	id bigint identity(1,1) not null PRIMARY key,
	订单号 varchar(120),
	班次号 varchar(120),--日期+  01(早班)/02(中班)/03(晚班)
	工序 varchar(120),
	材料名称 varchar(120),
	材料规格 varchar(120),
	材料用量 decimal
);

insert into test_produce (订单号,班次号,工序,材料名称,材料规格,材料用量) values ('s001','2024070101','印刷','塑料膜1号','500*10',100);
insert into test_produce (订单号,班次号,工序,材料名称,材料规格,材料用量) values ('s001','2024070102','印刷','塑料膜2号','500*10',50);
insert into test_produce (订单号,班次号,工序,材料名称,材料规格,材料用量) values ('s001','2024070103','印刷','塑料膜3号','500*10',200);
insert into test_produce (订单号,班次号,工序,材料名称,材料规格,材料用量) values ('s001','2024070201','分切','封标1号','20*10',100);
insert into test_produce (订单号,班次号,工序,材料名称,材料规格,材料用量) values ('s001','2024070202','分切','封标2号','30*10',200);
insert into test_produce (订单号,班次号,工序,材料名称,材料规格,材料用量) values ('s001','2024070203','分切','封标3号','20*10',20);

sql:

每单只有一条数据反应各工序材料规格和损耗情况,材料规格只取每种工序第一条,用量损耗取该工序合计

逻辑上相当于行转列,但是由于实际业务场景中业务逻辑和表结构都更复杂,不好复用,而且本例子主要解决分组排序问题,所以不采用 pivot ,而使用效率低的子查询关联;

select * from (
select ridYs.订单号,材料名称,材料规格,印刷总用量 from 
--进行分组排序,将单号,和材料名称,规格这些不进行合计的字段机型取第一行操作
(select rid = ROW_NUMBER()  OVER (partition by 订单号,工序 order by 班次号),订单号,材料名称,材料规格 from test_produce 
where 工序 ='印刷') --印刷工具第一行,所以取工序为印刷)
 as ridYs  --印刷取首行子查询 
 left join 
 --对每单的工序的用量进行加总
 (select 订单号,sum(材料用量) 印刷总用量 from test_produce where 工序 ='印刷' group by 工序,订单号 ) as  sumYs 
 on ridYs.订单号=sumYs.订单号 where rid=1 ) as ys
 left join

 --分切同理
( select ridYs.订单号,材料名称,材料规格,分切总用量 from 
 (select rid = ROW_NUMBER()  OVER (partition by 订单号,工序 order by 班次号),订单号,材料名称,材料规格 from test_produce 
 where 工序 ='分切')as ridYs   left join
 (select 订单号,sum(材料用量) 分切总用量 from test_produce where 工序 ='分切' group by 工序,订单号 ) as  sumYs 
 on ridYs.订单号=sumYs.订单号 where rid=1 )fq on ys.订单号=fq.订单号 
 where ys.订单号='s001'

结果:

总结

正常来说,肯定不应该写效率低下的关联子查询的,而应该把子查询直接写成视图,关联查询直接查视图会好些,博主这么写是因为一方面这个用例讲解重点并不是视图,另一方面也因为有些场景确实不方便使用视图,比如博主做的是用友报表开发,上面的逻辑是写在存储过程中国,数据源是来自于临时表或者外部链接,就无法使用视图.