/*原资料
id Bno Rno width
------------------------------------------------
1 F11082803F1 110901001 850
2 F11082803F1 110901002 700
3 F11082803F1 110901003 850
4 F11082803F1 110901004 700
5 A11083114B5 110901601 440
6 A11083114B5 110901602 440
7 A11083114B5 110901603 440
8 A11083114B5 110901604 240
9 A11083114B5 110901605 440
10 A11083114B5 110901606 440
11 A11083114B5 110901607 440
12 A11083114B5 110901608 240
*/
希望得到以下SELECT结果:id bno rno width bqty Frac
----------- -------------------- -------------------- ----------- ----------- -------------------------
1 F11082803F1 110901001 850 850 1/2
2 F11082803F1 110901002 700 1550 2/2
3 F11082803F1 110901003 850 850 1/2
4 F11082803F1 110901004 700 1550 2/2
5 A11083114B5 110901601 440 440 1/4
6 A11083114B5 110901602 440 880 2/4
7 A11083114B5 110901603 440 1320 3/4
8 A11083114B5 110901604 240 1560 4/4
9 A11083114B5 110901605 440 440 1/4
10 A11083114B5 110901606 440 880 2/4
11 A11083114B5 110901607 440 1320 3/4
12 A11083114B5 110901608 240 1560 4/4
补充一下:
对比规则是:
1.当前记录中的 BNO字段如果与上条记录的如果相同
2.BQty显示当前记录中的WIDTH字段加上上条记录的 WIDTH
3.ium 显示操作次数,如果当前记录中的WIDTH字段加上上条记录的 WIDTH >=1550则下条记录从1开始
*/
INSERT @TB
SELECT 1, 'F11082803F1', '110901001', 850 UNION ALL
SELECT 2, 'F11082803F1', '110901002', 700 UNION ALL
SELECT 3, 'F11082803F1', '110901003', 850 UNION ALL
SELECT 4, 'F11082803F1', '110901004', 700 UNION ALL
SELECT 5, 'A11083114B5', '110901601', 440 UNION ALL
SELECT 6, 'A11083114B5', '110901602', 440 UNION ALL
SELECT 7, 'A11083114B5', '110901603', 440 UNION ALL
SELECT 8, 'A11083114B5', '110901604', 240 UNION ALL
SELECT 9, 'A11083114B5', '110901605', 440 UNION ALL
SELECT 10, 'A11083114B5', '110901606', 440 UNION ALL
SELECT 11, 'A11083114B5', '110901607', 440 UNION ALL
SELECT 12, 'A11083114B5', '110901608', 240;WITH C AS
(
SELECT bqty=width,Frac=1,*FROM @TB WHERE id=1
UNION ALL
SELECT CASE WHEN T.[Bno]=C.[Bno]
THEN CASE WHEN C.bqty>=1550 THEN T.[width] ELSE C.bqty+T.width END
ELSE T.width
END,
CASE WHEN T.[Bno]=C.[Bno]
THEN CASE WHEN C.bqty>=1550 THEN 1 ELSE Frac+1 END
ELSE 1
END, T.*
FROM @TB AS T JOIN C ON T.id=C.id+1
)
SELECT id,Bno,Rno,width,bqty,LTRIM(Frac)+'/'+LTRIM(COUNT(*) OVER (PARTITION BY id-Frac)) AS Frac
FROM C
/*
id Bno Rno width bqty Frac
----------- ----------- --------- ----------- ----------- -------------------------
1 F11082803F1 110901001 850 850 1/2
2 F11082803F1 110901002 700 1550 2/2
3 F11082803F1 110901003 850 850 1/2
4 F11082803F1 110901004 700 1550 2/2
5 A11083114B5 110901601 440 440 1/4
6 A11083114B5 110901602 440 880 2/4
7 A11083114B5 110901603 440 1320 3/4
8 A11083114B5 110901604 240 1560 4/4
9 A11083114B5 110901605 440 440 1/4
10 A11083114B5 110901606 440 880 2/4
11 A11083114B5 110901607 440 1320 3/4
12 A11083114B5 110901608 240 1560 4/4
*/