一个复杂的查询
数据库：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 SL
UNION 
SELECT 'X01' AS BH,12 AS SL
UNION 
SELECT 'X01' AS BH,-2 AS SL
UNION 
SELECT 'X01' AS BH,-17 AS SL
UNION 
SELECT 'X01' AS BH,16 AS SL
UNION 
SELECT 'X02' AS BH,3 AS SL
UNION 
SELECT 'X02' AS BH,1 AS SL
UNION 
SELECT 'X02' AS BH,-2 AS SL
UNION 
SELECT 'X02' AS BH,-1 AS SL
UNION 
SELECT 'X02' AS BH,12 AS SL
UNION 
SELECT 'X03' AS BH,3 AS SL
UNION 
SELECT 'X03' AS BH,12 AS SL
UNION 
SELECT 'X03' AS BH,-2 AS SL
UNION 
SELECT 'X03' AS BH,-1 AS SL
UNION 
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