根据B表更新A表字段问题

jamk 2013-08-14 04:09:04
小弟不才，今日碰到一个表更新问题，求大神指点，建立环境如下：


--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中记录条数不能少！！！！
...全文
285 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
tcmakebest 2013-08-15
  • 打赏
  • 举报
 回复
这种更新存在记录与记录之间的关系问题，需要循环操作，更新多条记录。 由于A表的排序规则未确定，不能得出一致的结果。 另外，A表的SUM一定得大于等于B表的值，否则也无法完成。
唐诗三百首 2013-08-14
  • 打赏
  • 举报
 回复
MSSQL TSQL转Oracle PLSQL语法: 1. case when..then..end --> decode() 2. CTE写法(即with..as(..)) --> 临时表
唐诗三百首 2013-08-14
  • 打赏
  • 举报
 回复 

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)
*/
發糞塗牆 2013-08-14
  • 打赏
  • 举报
 回复
又是发错版？我不保证哦，CTE好像是标准sql的东西，应该可以，但是我不会oracle，所以无法保证
jamk 2013-08-14
  • 打赏
  • 举报
 回复
引用 10 楼 DBA_Huangzj 的回复:
大概就这样，不过你的A表没有标识列，update的时候会有问题

--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
 
--版主大大，这段ORACLE也适用吧？。。
發糞塗牆 2013-08-14
  • 打赏
  • 举报
 回复
大概就这样，不过你的A表没有标识列，update的时候会有问题


--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

 
jamk 2013-08-14
  • 打赏
  • 举报
 回复
引用 7 楼 yangsh0722 的回复:
[quote=引用 5 楼 KONGMAJIAN 的回复:] [quote=引用 3 楼 yangsh0722 的回复:] [quote=引用 2 楼 KONGMAJIAN 的回复:] [quote=引用 1 楼 yangsh0722 的回复:] 给你个示例 update B set B.i1=A.i1 from A where B.iid=A.iid
哥，你这是更新B表？麻烦仔细看下需求，谢谢！[/quote] 你这什么需求啊？直接把A表中满足A.ID=B.ID ADN A.SKU=B.SKU第一条记录的的A.QTY改为B.QTY，其他的都是0不就可以了[/quote] 忘了说了，不能这么干。必须最大程度保留原有数据（B中的QTY其实很大部分等于A的SUM(QTY)），也就是说尽量少更新A的QTY。[/quote] 只更新 sum(a.qty)<>b.qty 不就少了很多了，正如版主说的，需要点其他什么规则才行。[/quote] 对的，我已经把sum(a.qty)=b.qty直接排除了，接下来就是要处理那些不等的了。。。
jamk 2013-08-14
  • 打赏
  • 举报
 回复
引用 4 楼 DBA_Huangzj 的回复:
你这个需求有点不是很明确，的确如3楼说的可以走极端。我觉得你应该还要有个参照列才行，你这个应该是类似出入库的问题吧，比如需要5个货物，
好吧，版主大大来了，说下实际情况吧，我这是个两个系统的对接，对，是入出库系统，其中A表在上位系统，B表是下位系统的,A表中的数据会出现相同ID以及SKU，但是QTY不同的记录，然后同步到B表，但是B表是汇总的，也就是说B表相同ID以及SKU只会出现一条，这样B表的数据是B.QTY=SUM(A.QTY)，然后，B表QTY字段会根据实际发出货品更新，也就是原本B中QTY是6的，可是实物只出了5，那么B的QTY就更新成了5，说了那么多，接下来就是重点了，我要根据B表反更新A表，但是要保证A表记录更新最少（因为上位系统的条件限制）。所以才会出现上述需求。
-Tracy-McGrady- 2013-08-14
  • 打赏
  • 举报
 回复
引用 5 楼 KONGMAJIAN 的回复:
[quote=引用 3 楼 yangsh0722 的回复:] [quote=引用 2 楼 KONGMAJIAN 的回复:] [quote=引用 1 楼 yangsh0722 的回复:] 给你个示例 update B set B.i1=A.i1 from A where B.iid=A.iid
哥，你这是更新B表？麻烦仔细看下需求，谢谢！[/quote] 你这什么需求啊？直接把A表中满足A.ID=B.ID ADN A.SKU=B.SKU第一条记录的的A.QTY改为B.QTY，其他的都是0不就可以了[/quote] 忘了说了，不能这么干。必须最大程度保留原有数据（B中的QTY其实很大部分等于A的SUM(QTY)），也就是说尽量少更新A的QTY。[/quote] 只更新 sum(a.qty)<>b.qty 不就少了很多了，正如版主说的，需要点其他什么规则才行。
發糞塗牆 2013-08-14
  • 打赏
  • 举报
 回复
要去A表找是否足够库存，然后每个类型要出多少个
jamk 2013-08-14
  • 打赏
  • 举报
 回复
引用 3 楼 yangsh0722 的回复:
[quote=引用 2 楼 KONGMAJIAN 的回复:] [quote=引用 1 楼 yangsh0722 的回复:] 给你个示例 update B set B.i1=A.i1 from A where B.iid=A.iid
哥，你这是更新B表？麻烦仔细看下需求，谢谢！[/quote] 你这什么需求啊？直接把A表中满足A.ID=B.ID ADN A.SKU=B.SKU第一条记录的的A.QTY改为B.QTY，其他的都是0不就可以了[/quote] 忘了说了，不能这么干。必须最大程度保留原有数据（B中的QTY其实很大部分等于A的SUM(QTY)），也就是说尽量少更新A的QTY。
發糞塗牆 2013-08-14
  • 打赏
  • 举报
 回复
你这个需求有点不是很明确，的确如3楼说的可以走极端。我觉得你应该还要有个参照列才行，你这个应该是类似出入库的问题吧，比如需要5个货物，
-Tracy-McGrady- 2013-08-14
  • 打赏
  • 举报
 回复
引用 2 楼 KONGMAJIAN 的回复:
[quote=引用 1 楼 yangsh0722 的回复:] 给你个示例 update B set B.i1=A.i1 from A where B.iid=A.iid
哥，你这是更新B表？麻烦仔细看下需求，谢谢！[/quote] 你这什么需求啊？直接把A表中满足A.ID=B.ID ADN A.SKU=B.SKU第一条记录的的A.QTY改为B.QTY，其他的都是0不就可以了
jamk 2013-08-14
  • 打赏
  • 举报
 回复
引用 1 楼 yangsh0722 的回复:
给你个示例 update B set B.i1=A.i1 from A where B.iid=A.iid
哥，你这是更新B表？麻烦仔细看下需求，谢谢！
-Tracy-McGrady- 2013-08-14
  • 打赏
  • 举报
 回复
给你个示例 update B set B.i1=A.i1 from A where B.iid=A.iid
MySQL轻松上手教程
其主要的作用是存储项目中的数据，例如用户数据，商品数据，订单数据，文章数据等等，本课程主要讲解 MySQL 数据库的常用操作，例如命令行下数据库的创建，表字段类型，表的创建，数据的增删改查等。除了在命令行下...
A表字段更新为B表字段值
案列场景：更新员工表（sec.sec_staff）的short_name字段值，short_name的值由甲方人员提供（Excel表格）。为实现通过SQL修改，创建临时表sec.sec_staff_temp，sec.sec_staff表结构及Excel详细数据此处略过，sql如下...
A表字段更新为B表的字段值
A表字段更新为B表的字段值   T1表 结构 id name school 1 ming1 清华大学 2 ming2 北京大学 3 ming3 复旦大学   T2表 结构 id student school 11 ming1 牛津大学 12
ORACLE A表根据B表字段更新
update product_info t set (t.salename,t.xsqy)=(select c.salename,c.xsqy from contract_erp c where c.contractcode=t.contractid) where t.contractid in(select c.contractcode from contract_erp c) and t.co
mysql 删除重复的记录、A表字段更新到B表
-- A表字段更新为B表的字段值，条件A\B表通过id关联的记录 update b_table as b set b.`name` = (select a.name FROM a_table as a where b.id=a.id) where EXISTS(select * from a_table as c where c.id=b.id); ...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章