作者jonce007 (汪汪)
看板Database
标题Re: [SQL ] update multiple-table 语法求教
时间Sun Aug 14 14:14:14 2016
※ 引述《JeremyJoung (J.J.)》之铭言：
: UPDATE `pb1`
: JOIN `pb1` AS `pb2` ON pb1.pbin=pb2.pbin && pb2.pbon<>''
: SET pb1.opi=pb1.opi+pb2.opi
: , pb1.oyd=pb1.oyd+pb2.oyd
: , pb1.okg=pb1.okg+pb2.okg
感谢Jeremy大的回应，
不过有两个小小的问题
第一次执行後的结果：
+-----------+-----------+------+------+------+------+------+------+
| pbin | pbon | ipi | iyd | ikg | opi | oyd | okg |
+-----------+-----------+------+------+------+------+------+------+
| 020417008 | | 51.0 | 6810 | 0.00 | 12.0 | 1601 | 0.00 |
| 020417008 | 020502009 | 0.0 | 0 | 0.00 | 12.0 | 1601 | 0.00 |
| 020417008 | 020518001 | 0.0 | 0 | 0.00 | 11.0 | 1401 | 0.00 |
| 020417008 | 020611001 | 0.0 | 0 | 0.00 | 28.0 | 3808 | 0.00 |
+-----------+-----------+------+------+------+------+------+------+
只有加到第一笔（row:2)
第二次执行後的结果：
+-----------+-----------+------+------+------+------+------+------+
| pbin | pbon | ipi | iyd | ikg | opi | oyd | okg |
+-----------+-----------+------+------+------+------+------+------+
| 020417008 | | 51.0 | 6810 | 0.00 | 24.0 | 3202 | 0.00 |
| 020417008 | 020502009 | 0.0 | 0 | 0.00 | 12.0 | 1601 | 0.00 |
| 020417008 | 020518001 | 0.0 | 0 | 0.00 | 11.0 | 1401 | 0.00 |
| 020417008 | 020611001 | 0.0 | 0 | 0.00 | 28.0 | 3808 | 0.00 |
+-----------+-----------+------+------+------+------+------+------+
一样只加到第一笔(row:2)，而且值会不断的重复加上去
有办法用sum(),或者在累加这三笔前先归零吗^^
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 111.241.224.25
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Database/M.1471155256.A.EC9.html