表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
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
Update wrk
Set quantity=quantity+A.quantity
From WCK As A
Where PF=A.PF And colt=A.colt And sizet=A.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
---更新
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)
Insert Into wck(kid,cid,khm,gcm,pf,colt,sizet,quantity,rkrq,rkcode,rkr)
Select kid,cid,khm,gcm,pf,colt,sizet,quantity,rkrq,rkcode,rkr From WrK Where Not Exists
(Select 1 From wck Where wrk.PF=WCK.PF And wrk.colt=WCK.colt And wrk.sizet=WCK.sizet)这样就可以了,但是我一点不明白 Select 1 ...这个SELECT 1 起什么作用?
只要满足where语句,select 1 就能为前面not exists提供有数据的判断依据
比select *方便快捷。