根据B表更新A表字段问题
小弟不才，今日碰到一个表更新问题，求大神指点，建立环境如下：
SQL
--USE TEST
--DROP TABLE A ;
--DROP TABLE B ;
CREATE TABLE A(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC);
CREATE TABLE B(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC);
INSERT INTO A
SELECT '1','abcde','1'
UNION ALL
SELECT '1','abcde','2'
UNION ALL
SELECT '1','abcde','3';
INSERT INTO B
SELECT '1','abcde','6';
SELECT * FROM A;
SELECT * FROM B;
--说明，需要根据B表QTY更新A表QTY,且更新后的SUM(A.QTY)=B.QTY
--条件是根据A.ID=B.ID ADN A.SKU=B.SKU
--举例
A表：
ID SKU QTY
1 ABCDE 1
1 ABCDE 2
1 ABCDE 3
B表:
1 ABCDE 5
结果：
A表：
ID SKU QTY
1 ABCDE 0
1 ABCDE 2
1 ABCDE 3
或者
A表：
ID SKU QTY
1 ABCDE 1
1 ABCDE 2
1 ABCDE 2
或者
A表：
ID SKU QTY
1 ABCDE 1
1 ABCDE 1
1 ABCDE 3
总之是SUM(A.QTY)=B.QTY，但是A中记录条数不能少！！！！
--DROP?TABLE?A?;
--DROP?TABLE?B?;
--CREATE?TABLE?A(ID?VARCHAR(5),SKU?VARCHAR(5),QTY?int);
--CREATE?TABLE?B(ID?VARCHAR(5),SKU?VARCHAR(5),QTY?int);
?
--INSERT?INTO?A
--SELECT?'1','abcde','1'
--UNION?ALL
--SELECT?'1','abcde','2'
--UNION?ALL
--SELECT?'1','abcde','3';
?
--INSERT?INTO?B
--SELECT?'1','abcde','5';
?
--SELECT?*?FROM?A;
--SELECT?*?FROM?B;
;WITH cte AS
(
SELECT a.*,b.qty AS tqty,b.qty-a.qty AS sqty,ROW_NUMBER()OVER(ORDER BY a.qty )oid,'noneedupdate' AS [needupdate]
FROM a INNER JOIN b ON a.id=b.id AND a.sku=b.sku
),cte2 AS
(
SELECT *
FROM cte
WHERE oid=1
UNION ALL
SELECT a.id,a.sku,a.qty,a.tqty,CASE WHEN b.sqty-a.qty>0 THEN b.sqty-a.qty ELSE b.sqty END sqty,a.oid,CASE WHEN b.sqty-a.qty>0 THEN 'noneedupdate' ELSE 'needupdate' END [needupdate]
FROM cte a INNER JOIN cte2 b ON a.oid=b.oid+1
)
--SELECT * FROM cte2
UPDATE a
SET a.qty=b.sqty
FROM a INNER JOIN cte2 b ON a.id=b.id AND a.sku=b.sku
WHERE b.[needupdate]='needupdate'
?
--说明，需要根据B表QTY更新A表QTY,且更新后的SUM(A.QTY)=B.QTY
--条件是根据A.ID=B.ID?ADN?A.SKU=B.SKU
?
CREATE TABLE A(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC)
CREATE TABLE B(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC)
-- 测试1
INSERT INTO A
SELECT '1','abcde','1'
UNION ALL
SELECT '1','abcde','2'
UNION ALL
SELECT '1','abcde','3'
INSERT INTO B
SELECT '1','abcde','5'
-- 更新1
with t1 as
(select row_number() over(partition by ID,SKU order by getdate()) 'rn',
ID,SKU,QTY from A),
t2 as
(select b.ID,b.SKU,a.QTY-b.QTY 'dq'
from
(select ID,SKU,QTY from B) b
inner join
(select ID,SKU,sum(QTY) 'QTY'
from A group by ID,SKU) a on b.ID=a.ID and b.SKU=a.SKU
where b.QTY<>a.QTY),
t3 as
(select a.rn,a.ID,a.SKU,a.QTY,
isnull((select sum(b.QTY) from t1 b
where b.ID=a.ID and b.SKU=a.SKU and b.rn<a.rn),0) 'hq'
from t1 a
)
update t3
set t3.QTY=case when t3.hq>=t2.dq then t3.QTY
when t3.hq+t3.QTY<=t2.dq then 0
when t3.hq+t3.QTY>t2.dq then t3.QTY-(t2.dq-t3.hq)
end
from t3
inner join t2 on t3.ID=t2.ID and t3.SKU=t2.SKU
-- 结果1
SELECT * FROM A
/*
ID SKU QTY
----- ----- ---------------------------------------
1 abcde 0
1 abcde 2
1 abcde 3
(3 row(s) affected)
*/
SELECT * FROM B
/*
ID SKU QTY
----- ----- ---------------------------------------
1 abcde 5
(1 row(s) affected)
*/
-- 测试2
truncate table A
truncate table B
INSERT INTO A
SELECT '1','abcde','6'
UNION ALL
SELECT '1','abcde','2'
UNION ALL
SELECT '1','abcde','3'
INSERT INTO B
SELECT '1','abcde','8'
-- 更新2
with t1 as
(select row_number() over(partition by ID,SKU order by getdate()) 'rn',
ID,SKU,QTY from A),
t2 as
(select b.ID,b.SKU,a.QTY-b.QTY 'dq'
from
(select ID,SKU,QTY from B) b
inner join
(select ID,SKU,sum(QTY) 'QTY'
from A group by ID,SKU) a on b.ID=a.ID and b.SKU=a.SKU
where b.QTY<>a.QTY),
t3 as
(select a.rn,a.ID,a.SKU,a.QTY,
isnull((select sum(b.QTY) from t1 b
where b.ID=a.ID and b.SKU=a.SKU and b.rn<a.rn),0) 'hq'
from t1 a
)
update t3
set t3.QTY=case when t3.hq>=t2.dq then t3.QTY
when t3.hq+t3.QTY<=t2.dq then 0
when t3.hq+t3.QTY>t2.dq then t3.QTY-(t2.dq-t3.hq)
end
from t3
inner join t2 on t3.ID=t2.ID and t3.SKU=t2.SKU
-- 结果2
SELECT * FROM A
/*
ID SKU QTY
----- ----- ---------------------------------------
1 abcde 3
1 abcde 2
1 abcde 3
(3 row(s) affected)
*/
SELECT * FROM B
/*
ID SKU QTY
----- ----- ---------------------------------------
1 abcde 8
(1 row(s) affected)
*/
