求助一条SQL

[cp3154]

A表
A10000011 10001 5
A10000012 10001 3

B表
10001 3

最终结果

C
A10000011 10001 =3-5 -2
A10000012 10001 =(-2)-3 -3

[卖水果的net]

这是什么规则？

[繁花尽流年]

--test data
IF OBJECT_ID('tempdb..#tmpA') IS NOT NULL DROP TABLE #tmpA
IF OBJECT_ID('tempdb..#tmpB') IS NOT NULL DROP TABLE #tmpB
CREATE TABLE #tmpA (seqno VARCHAR(20),code INT, VALUE INT)
INSERT INTO #tmpA (seqno,code,[VALUE])
VALUES('A10000011',10001,5),('A10000012',10001,3)

CREATE TABLE #tmpB (code INT, VALUE INT)	
INSERT INTO #tmpB (code,[VALUE])
VALUES(10001,3)

--code
;WITH a AS (
	SELECT seqno,code,[VALUE]*(-1) AS [VALUE],ROW_NUMBER() OVER (PARTITION BY code ORDER BY seqno) AS rno
	FROM #tmpA
)

SELECT t3.seqno,t2.code,t2.[VALUE] 
FROM 
(
	SELECT t1.code,SUM(t1.[VALUE]) OVER (PARTITION BY t1.code ORDER BY t1.rno) AS [VALUE],t1.rno
	FROM (
		SELECT code,[VALUE],rno
		FROM a
		UNION
		SELECT code,[VALUE],0 AS rno FROM #tmpB
	) t1
) t2
INNER JOIN (
		SELECT seqno,code,rno
		FROM a	
) t3 ON t3.rno = t2.rno AND t3.code = t2.code

seqno code VALUE -------------------- ----------- ----------- A10000011 10001 -2 A10000012 10001 -5 猜你样张是不是搞错了第二条递减应该是-5吧

[cp3154]

引用 2 楼 zengertao 的回复:

--test data
IF OBJECT_ID('tempdb..#tmpA') IS NOT NULL DROP TABLE #tmpA
IF OBJECT_ID('tempdb..#tmpB') IS NOT NULL DROP TABLE #tmpB
CREATE TABLE #tmpA (seqno VARCHAR(20),code INT, VALUE INT)
INSERT INTO #tmpA (seqno,code,[VALUE])
VALUES('A10000011',10001,5),('A10000012',10001,3)

CREATE TABLE #tmpB (code INT, VALUE INT)	
INSERT INTO #tmpB (code,[VALUE])
VALUES(10001,3)

--code
;WITH a AS (
	SELECT seqno,code,[VALUE]*(-1) AS [VALUE],ROW_NUMBER() OVER (PARTITION BY code ORDER BY seqno) AS rno
	FROM #tmpA
)

SELECT t3.seqno,t2.code,t2.[VALUE] 
FROM 
(
	SELECT t1.code,SUM(t1.[VALUE]) OVER (PARTITION BY t1.code ORDER BY t1.rno) AS [VALUE],t1.rno
	FROM (
		SELECT code,[VALUE],rno
		FROM a
		UNION
		SELECT code,[VALUE],0 AS rno FROM #tmpB
	) t1
) t2
INNER JOIN (
		SELECT seqno,code,rno
		FROM a	
) t3 ON t3.rno = t2.rno AND t3.code = t2.code

seqno code VALUE -------------------- ----------- ----------- A10000011 10001 -2 A10000012 10001 -5 猜你样张是不是搞错了第二条递减应该是-5吧

第二条的话会判断，如果第一条结果已经为负，则按0处理~

[繁花尽流年]

引用 3 楼 cp3154 的回复:

[quote=引用 2 楼 zengertao 的回复:]

--test data
IF OBJECT_ID('tempdb..#tmpA') IS NOT NULL DROP TABLE #tmpA
IF OBJECT_ID('tempdb..#tmpB') IS NOT NULL DROP TABLE #tmpB
CREATE TABLE #tmpA (seqno VARCHAR(20),code INT, VALUE INT)
INSERT INTO #tmpA (seqno,code,[VALUE])
VALUES('A10000011',10001,5),('A10000012',10001,3)

CREATE TABLE #tmpB (code INT, VALUE INT)	
INSERT INTO #tmpB (code,[VALUE])
VALUES(10001,3)

--code
;WITH a AS (
	SELECT seqno,code,[VALUE]*(-1) AS [VALUE],ROW_NUMBER() OVER (PARTITION BY code ORDER BY seqno) AS rno
	FROM #tmpA
)

SELECT t3.seqno,t2.code,t2.[VALUE] 
FROM 
(
	SELECT t1.code,SUM(t1.[VALUE]) OVER (PARTITION BY t1.code ORDER BY t1.rno) AS [VALUE],t1.rno
	FROM (
		SELECT code,[VALUE],rno
		FROM a
		UNION
		SELECT code,[VALUE],0 AS rno FROM #tmpB
	) t1
) t2
INNER JOIN (
		SELECT seqno,code,rno
		FROM a	
) t3 ON t3.rno = t2.rno AND t3.code = t2.code

seqno code VALUE -------------------- ----------- ----------- A10000011 10001 -2 A10000012 10001 -5 猜你样张是不是搞错了第二条递减应该是-5吧

第二条的话会判断，如果第一条结果已经为负，则按0处理~[/quote]

--code
;WITH a AS (
	SELECT '0' AS seqno,code,[VALUE],0 AS rno FROM #tmpB
	UNION
    SELECT seqno,code,[VALUE]*(-1) AS [VALUE],ROW_NUMBER() OVER (PARTITION BY code ORDER BY seqno) AS rno
    FROM #tmpA
)
, b AS
(
		SELECT seqno,code,[VALUE],rno 
		FROM a
		WHERE rno=0
		UNION ALL
		SELECT a.seqno,a.code,a.[VALUE]+IIF(b.[VALUE]<0,0,b.[VALUE]) AS [VALUE],a.rno
		FROM a
		INNER JOIN b ON a.code=b.code AND a.rno=b.rno+1
)
SELECT * FROM b 
WHERE rno<>0
OPTION (MAXRECURSION 10);

问问题要把需求描述清楚啊

[RINK_1]

B表记录的是期初数？ 借用#2的数据

with cte
as
(select * from #tmpA A
 outer apply (select SUM(value) as subtotal from #tmpA where seqno<=A.seqno AND A.code=code) AS B)
 
select A.*,
CASE WHEN subtotal-A.VALUE>B.VALUE THEN 0-A.VALUE ELSE B.VALUE-A.subtotal END AS BALANCE
from cte A 
join #tmpB B ON 1=1 AND A.code=B.code

[繁花尽流年]

引用 5 楼 RINK_1 的回复:

B表记录的是期初数？ 借用#2的数据

with cte
as
(select * from #tmpA A
 outer apply (select SUM(value) as subtotal from #tmpA where seqno<=A.seqno AND A.code=code) AS B)
 
select A.*,
CASE WHEN subtotal-A.VALUE>B.VALUE THEN 0-A.VALUE ELSE B.VALUE-A.subtotal END AS BALANCE
from cte A 
join #tmpB B ON 1=1 AND A.code=B.code

思路很好，但我感觉这么写subtotal遇到正负值很多的value记录时会不会有问题