这是一个很有趣SQL数据处理应用,具体需求如下
ERP需要工程将物料编码相邻的编码合并求和BOM用量,巧妙的用到了已有排序号与分组排序号之间的差值求解
示例：
原数据：
要求转换：
实际转换后数据：
SQL代码实现:
with tab1 as ( select 1 as [Rank] ,1710 as OPERATION_CODE,3.621000 UNIT_TIME UNION select 2 as [Rank] ,1710 as OPERATION_CODE,3.621000 UNIT_TIME UNION select 3 as [Rank] ,1711 as OPERATION_CODE,3.333000 UNIT_TIME UNION select 4 as [Rank] ,1710 as OPERATION_CODE,0.288000 UNIT_TIME UNION select 5 as [Rank] ,1711 as OPERATION_CODE,0.288000 UNIT_TIME UNION select 6 as [Rank] ,1711 as OPERATION_CODE,3.333000 UNIT_TIME ) SELECT * into #tab1 FROM tab1 SELECT * FROM #tab1 SELECT MAX([Rank]) [Rank],OPERATION_CODE,SUM(UNIT_TIME) UNIT_TIME FROM (SELECT OPERATION_CODE,[Rank],UNIT_TIME,row_number()over(partition by OPERATION_CODE order by [Rank]) -[Rank] DiffVal FROM #tab1) a GROUP BY OPERATION_CODE,DiffVal ORDER BY MAX([Rank])
原文：https://www.cnblogs.com/pcbren/p/9270827.html