22,209
社区成员
发帖
与我相关
我的任务
分享
---有如下几列假定为
ID A B C D E F
1 162 2 NULL 1 2 1
1 163 60 NULL 1 60 1
1 233 60 163 61 120 2
1 234 16 163 121 136 3
---有如下几列假定为
ID A B C D E F
1 162 2 NULL 1 2 1
1 163 60 NULL 3 62 2
1 233 60 163 63 122 3
1 234 16 163 123 138 4
/* 测试数据
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
GO
CREATE TABLE TA(
A int,
B int,
C int,
D int,
E int,
F int
)
GO
;WITH t(A,B,C,D,E,F) AS (
SELECT 162,2,NULL,1,2,1 UNION ALL
SELECT 163,60,NULL,1,60,1 UNION ALL
SELECT 233,60,163,61,120,2 UNION ALL
SELECT 234,16,163,121,136,3
)
INSERT INTO TA
SELECT *
FROM t
*/
-- F 最简单,按 A 的次序编号
;WITH t AS (
SELECT A,
ROW_NUMBER() OVER(ORDER BY A) rn
FROM ta
)
UPDATE ta
SET F = t.rn
FROM ta, t
WHERE ta.A = t.A
-- 只要统计出之前B的合计,D、E也很好算
;WITH t AS (
SELECT ta.A,
s.PrevB
FROM ta
CROSS APPLY (SELECT ISNULL(SUM(p.B),0) PrevB
FROM ta p
WHERE p.A < ta.A
) s
)
UPDATE ta
SET ta.D = t.PrevB + 1,
ta.E = t.PrevB + ta.B
FROM ta, t
WHERE ta.A = t.A
-- 结果
SELECT *
FROM TA
A B C D E F
----------- ----------- ----------- ----------- ----------- -----------
162 2 NULL 1 2 1
163 60 NULL 3 62 2
233 60 163 63 122 3
234 16 163 123 138 4
/* 测试数据
DELETE FROM TA
;WITH t(A,B,C,D,E,F) AS (
SELECT 162,3,NULL,1,3,1 UNION ALL
SELECT 163,60,NULL,1,60,1 UNION ALL
SELECT 233,60,163,61,120,2 UNION ALL
SELECT 234,16,163,121,136,3 UNION ALL
SELECT 299,2,162,4,5,2
)
INSERT INTO TA
SELECT *
FROM t
*/
-- F 按 (分割前的A、当前A) 的次序编号
;WITH t AS (
SELECT A,
ROW_NUMBER() OVER(ORDER BY ISNULL(C,A), A) rn
FROM ta
)
UPDATE ta
SET F = t.rn
FROM ta, t
WHERE ta.A = t.A
-- 只要统计出之前B的合计,D、E也很好算
;WITH t AS (
SELECT ta.A,
s.PrevB
FROM ta
CROSS APPLY (SELECT ISNULL(SUM(p.B),0) PrevB
FROM ta p
WHERE p.F < ta.F -- 判断前后的改动
) s
)
UPDATE ta
SET ta.D = t.PrevB + 1,
ta.E = t.PrevB + ta.B
FROM ta, t
WHERE ta.A = t.A
-- 结果
SELECT *
FROM TA
A B C D E F
----------- ----------- ----------- ----------- ----------- -----------
162 3 NULL 1 3 1
163 60 NULL 6 65 3
233 60 163 66 125 4
234 16 163 126 141 5
299 2 162 4 5 2
---有如下几列假定为
ID A B C D E F
1 162 3 NULL 1 3 1
1 163 60 NULL 1 60 1
1 233 60 163 61 120 2
1 234 16 163 121 136 3
1 299 2 162 4 5 2
那么结果应该变成这样的:
---有如下几列假定为
ID A B C D E F
1 162 3 NULL 1 3 1
1 163 60 NULL 6 65 3
1 233 60 163 66 125 4
1 234 16 163 126 141 5
1 299 2 162 4 5 2
就是说被拆开的记录 整体排序也是要按照未拆之前来排的