ID doc amount
1 123 2
2 1234 -1
3 12345 -1
4 3234 40
5 3242 -40
6 99 98
7 323 90
8 42 -32我想查出其中amount 相加为0的记录 比如 2 -1 -1; 40 -40
1 123 2
2 1234 -1
3 12345 -1
4 3234 40
5 3242 -40
6 99 98
7 323 90
8 42 -32我想查出其中amount 相加为0的记录 比如 2 -1 -1; 40 -40
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-30 10:05:21
-------------------------------------
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (ID INT,doc INT,amount INT)
INSERT INTO [tb]
SELECT 1,123,2 UNION ALL
SELECT 2,1234,-1 UNION ALL
SELECT 3,12345,-1 UNION ALL
SELECT 4,3234,40 UNION ALL
SELECT 5,3242,-40 UNION ALL
SELECT 6,99,98 UNION ALL
SELECT 7,323,90 UNION ALL
SELECT 8,42,-32--SQL查询如下:GOCREATE FUNCTION dbo.fn_GetAmount(@ID int,@Amount INT)
RETURNS @t TABLE(ID INT,total_amount INT,flag VARCHAR(1000))
AS
BEGIN
;WITH LiangLoveLan AS
(
SELECT ID,@Amount + amount AS total_amount,
CAST(RTRIM(@ID) + '.' + RTRIM(ID) AS VARCHAR(1000)) AS flag
FROM tb WHERE ID <> @ID
UNION ALL
SELECT B.ID,A.total_amount + B.amount,
CAST(A.flag + ',' + RTRIM(B.ID) AS VARCHAR(1000))
FROM LiangLoveLan AS A
JOIN tb AS B
ON A.total_amount + B.amount = 0
AND B.ID >= A.ID
AND CHARINDEX('.'+RTRIM(B.ID)+'.','.'+A.flag+'.')=0
)
INSERT @t SELECT * FROM LiangLoveLan;
RETURN;
END
GOSELECT *
FROM tb AS A
CROSS APPLY dbo.fn_GetAmount(ID,amount) AS B
WHERE total_amount = 0;GO
DROP TABLE tb;
DROP FUNCTION dbo.fn_GetAmount/*
ID doc amount ID total_amount flag
----------- ----------- ----------- ----------- ------------ ----------------1 123 2 3 0 1.2,3
2 1234 -1 3 0 2.1,3
3 12345 -1 2 0 3.1,2
4 3234 40 5 0 4.5
5 3242 -40 4 0 5.4(5 行受影响)
*/