问题

https://stackoverflow.com/questions/70465767/sum-values-by-sequence-number-and-group-by-flag

We have a list with a sequence number. The sequence will break, then begin again.

As you can see below, theSalesOrderLineis missing the number 4.

SalesOrder SalesOrderLine MStockCode MPrice MBomFlag

000000000182181 1 901337 0.00000 P

000000000182181 2 901335 2476.90000 C

000000000182181 3 340151 0.00000 C

000000000182181 5 900894 0.00000 P

000000000182181 6 400379 0.00000 C

000000000182181 7 900570 600.90000 C

What I'm looking to do is summarize theMPricefield by a consecutive number sequence, then use theMBomFlagfield to pick the "parent".

This would be the expected result of the above data. Any grouping will be done on theMBomFlagfield if the value =P

SalesOrder SalesOrderLine MStockCode MPrice MBomFlag

000000000182181 1 901337 2476.90000 P

000000000182181 5 900894 600.90000 P

What would be the best way to attack this? I'm trying to figure out something usingRANK(),ROW_NUMBER(),LEAD, andLAGbut not having much success

Here is the source data:

CREATE TABLE #SalesOrder (

SalesOrder NVARCHAR(20),

SalesOrderLine INT,

MStockCode INT,

MPrice DECIMAL(18,2),

MBomFlag VARCHAR(1))

INSERT INTO #SalesOrder (SalesOrder, SalesOrderLine, MStockCode, MPrice, MBomFlag)

SELECT '000000000182181', 1, '901337', 0.00000, 'P'

UNION

SELECT '000000000182181', 2, '901335', 2476.90000, 'C'

UNION

SELECT '000000000182181', 3, '340151', 0.00000, 'C'

UNION

SELECT '000000000182181', 5, '900894', 0.00000, 'P'

UNION

SELECT '000000000182181', 6, '400379', 0.00000, 'C'

UNION

SELECT '000000000182181', 7, '900570', 2600.90000, 'C'

SELECT *

FROM #SalesOrder

DROP TABLE #SalesOrder

解答

对SalesOrderLine 有序的数据，每当前后记录的 SalesOrderLine 相差不为 1 时分组，找出组内 MbomFlag 为 P 的记录，并且将 Mprice 的值改为组内 Mprice 求和。用SQL解决这个问题，需要先借助窗口函数创建标识，再根据标识分组，最后再用case语句找出结果。通常的办法是读出来用Python或SPL来做， SPL（一种 Java 的开源包）更容易被Java应用集成，代码也更简单一点，只要三句：

SPL源代码：https://github.com/SPLWare/esProc

问答搜集