接着学习! 再请问一个SQL语句
表wrk
kid cid khm gcm pf colt sizet quantity rqrq sh shrq rkr shr rkcode
1 7 玛格丽格 大河 CMM17-7610 31 M 10 07-09-20 0 07-09-20 admin aa rk0709210001
1 7 玛格丽格 大河 CMM17-7610 31 L 10 07-09-20 0 07-09-20 admin aa rk0709210001
1 7 玛格丽格 大河 CMM17-7610 31 LL 10 07-09-20 0 07-09-20 admin aa rk0709210001
表WCK
kid cid khm gcm pf colt sizet quantity rqrq rkcode
1 7 玛格丽格 大河 CMM17-7610 31 M 10 07-09-20 rk0709210001
两个问题
如果WRK中的PF,COLT,SIZET和WCK表中的PF,COT,SIZET相同则数量相加并更新WCK表
如果没有则在WCK里追加记录
想要得到的结果如下:
wck(表)
kid cid khm gcm pf colt sizet quantity rqrq rkcode
1 7 玛格丽格 大河 CMM17-7610 31 M 20 07-09-20 rk0709210001
1 7 玛格丽格 大河 CMM17-7610 31 L 10 07-09-20 rk0709210001
1 7 玛格丽格 大河 CMM17-7610 31 LL 10 07-09-20 rk0709210001
[解决办法]
---If Exists(Select 1 From wrk As A,wck As B Where A.PF=B.PF And A.colt=B.colt And A.sizet=B.sizet)
---更新
Update wrk
Set quantity=quantity+A.quantity
From (Select PF,COLT,SIZET,Sum(quantity) As quantity From WCK Group By PF,COLT,SIZET) As A
Where PF=A.PF And colt=A.colt And sizet=A.sizet
---Else
---插入
Insert Into wrk(kid,cid,khm,gcm,pf,colt,sizet,quantity,rqrq,rkcode)
Select kid,cid,khm,gcm,pf,colt,sizet,quantity,rqrq,rkcode From WCK Where Not Exists
(Select 1 From wrk Where PF=WCK.PF And colt=WCK.colt And sizet=WCK.sizet)
[解决办法]
Select 1 From wck Where wrk.PF=WCK.PF And wrk.colt=WCK.colt And wrk.sizet=WCK.sizet
只要满足where语句，select 1 就能为前面not exists提供有数据的判断依据
比select *方便快捷。
