数据库：SQL2000 +SP4
数据中有BH 和 SL 两个字段 SL 有正数有负数 要求相同BH的SL 正数、负数相抵 只显示正数不显示负数。
但是相同BH的数量合计不变
表结构如下
- SQL code
CREATE TABLE #S(BH VARCHAR(5) NULL, SL DECIMAL(8,2) NULL, XH INT IDENTITY)INSERT INTO #S(BH,SL)SELECT 'X01' AS BH,3 AS SLUNION SELECT 'X01' AS BH,12 AS SLUNION SELECT 'X01' AS BH,-2 AS SLUNION SELECT 'X01' AS BH,-17 AS SLUNION SELECT 'X01' AS BH,16 AS SLUNION SELECT 'X02' AS BH,3 AS SLUNION SELECT 'X02' AS BH,1 AS SLUNION SELECT 'X02' AS BH,-2 AS SLUNION SELECT 'X02' AS BH,-1 AS SLUNION SELECT 'X02' AS BH,12 AS SLUNION SELECT 'X03' AS BH,3 AS SLUNION SELECT 'X03' AS BH,12 AS SLUNION SELECT 'X03' AS BH,-2 AS SLUNION SELECT 'X03' AS BH,-1 AS SLUNION SELECT 'X03' AS BH,12 AS SL
要求结果
BH SL
X01 3
X01 9
X02 1
X02 3
X02 9
X03 3
X03 9
------解决方案--------------------
-- SQL2000
-- SQL2000
SELECT * ,IDENTITY(INT ,1,1 ) RN INTO #SS
FROM ( SELECT BH,SL
FROM #S A
WHERE NOT EXISTS ( SELECT *
FROM #S B
WHERE A.BH = B.BH
AND A.SL = 0 - B.SL )
AND A.SL > 0
UNION ALL
SELECT DISTINCT BH, 0
FROM #S A
WHERE NOT EXISTS ( SELECT *
FROM #S B
WHERE A.BH = B.BH
AND A.SL = 0 - B.SL )
AND A.SL > 0
)T
ORDER BY BH ,SL
SELECT BH,SL - (SELECT SUM(SL ) FROM #SS B WHERE A.BH = B.BH AND A.RN > B.RN ) SL FROM #SS A
WHERE A.SL >0
IF OBJECT_ID ('tempdb..#SS') IS NOT NULL
DROP TABLE #SS
-- SQL2005 以上
-- SQL2005 以上
;
WITH CTE
AS ( SELECT BH ,SL
FROM #S A
WHERE NOT EXISTS ( SELECT *
FROM #S B
WHERE A.BH = B.BH
AND A.SL = 0 - B.SL )
AND A.SL > 0),
CTE1
AS ( SELECT BH ,SL
FROM CTE
UNION ALL
SELECT DISTINCT BH ,0
FROM CTE ),
CTE2
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY BH, SL ) RN ,
*
FROM CTE1)
SELECT BH ,
SL - ( SELECT SUM(SL)
FROM CTE2 B
WHERE A.BH = B.BH
AND A.RN > B.RN
) SL
FROM CTE2 A
WHERE SL > 0