請教 SQL 指令的寫法（MySQL 環境）

sql mysql

kjfang 2019-04-24 01:12:17 ‧ 3001 瀏覽

分享至

先說明：小弟使用的資料庫環境是 MySQL 5.7

用下面三個來模擬交易，並說明我寫的SQL指令

table1 跟 table2 是資料源， table3 則是交易紀錄
table1的內容如下
t01_01(pk), t01_02(數值)
1111, 0
2222, 1000

table1的內容如下
t02_01(pk), t02_02(數值)
AAAA, 0
BBBB, 0
CCCC, 5000
DDDD, 8000

table3（交易紀錄）的內容如下
t03_01(t1的fk), t03_02(t2的fk), t03_03交易內容, t03_04(備註)
1111, AAAA, 400,
1111, BBBB, 500,

table3 紀錄了 table1跟table2交易的內容，由上面資料顯示：
table1 的 1111 跟 table2 的 AAAA 交易了一次 400
table1 的 1111 跟 table2 的 BBBB 交易了一次 500

現在要取消這兩筆交易
將數值分別加回 table1、table2相應的資料內，並在table3的資料備註欄裡面加註取消

小弟的 SQL 這樣寫

UPDATE test01, test02, test03
SET
t01_02 = t01_02 + t03_03,
t02_02 = t02_02 + t03_03,
t03_04 = 'Void'
WHERE t01_01 = t03_01 AND t02_01 = t03_02;

期望的結果是：
table1的1111數值有900（返回兩筆400+500）；
table2的AAAA數值有400、BBB有500；
table3的兩筆紀錄備註欄寫入Void

但執行的結果是 table2、table3 的資料都正常，table1的資料卻只會返回第一筆交易的數值
請教各位大德，小弟該如何修正這個 SQL 指令？

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2019-04-24 14:19:43

最佳解答

A.整張取消(test03.t03_01='1111')

UPDATE test01, test02, test03,

(select t03_01,sum(t03_03) as t03_03X
from test03
where 1=1
AND t03_04 <> 'Void'
group by t03_01
) AS AA ,

(select t03_01,t03_02,sum(t03_03) as t03_03Y
from test03
where 1=1
AND t03_04 <> 'Void'
group by t03_01,t03_02
) AS BB 

SET
t01_02 = t01_02 + t03_03X,
t02_02 = t02_02 + t03_03Y,
t03_04 =  'Void'

WHERE 1=1
AND test03.t03_01='1111'
AND test03.t03_01=test01.t01_01
AND test03.t03_02=test02.t02_01
AND test03.t03_01=AA.t03_01
AND test03.t03_02=BB.t03_02
AND test03.t03_01=BB.t03_01

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=64c8c52597df4eff67deea50b31f35da

B.指定單筆取消(test03.t03_01='1111',test03.t03_02='AAAA')

UPDATE test01, test02, test03,

(select t03_01,t03_02,sum(t03_03) as t03_03X
from test03
where 1=1
AND t03_04 <> 'Void'
group by t03_01,t03_02
) AS AA ,

(select t03_01,t03_02,sum(t03_03) as t03_03Y
from test03
where 1=1
AND t03_04 <> 'Void'
group by t03_01,t03_02
) AS BB 

SET
t01_02 = t01_02 + t03_03X,
t02_02 = t02_02 + t03_03Y,
t03_04 =  'Void'

WHERE 1=1
AND test03.t03_01='1111'
AND test03.t03_02='AAAA'
AND test03.t03_01=test01.t01_01
AND test03.t03_02=test02.t02_01
AND test03.t03_01=AA.t03_01
AND test03.t03_02=BB.t03_02
AND test03.t03_01=BB.t03_01
AND test03.t03_02=AA.t03_02

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ddf3e7e189fd101ef29464e4738f5a4e

回應 2
分享
檢舉

kjfang iT邦新手 5 級 ‧ 2019-04-25 16:45:03 檢舉

感謝 rogeryao 大大
在我PC的資料庫測試，這樣的寫法彈性又實用。
正在改寫到公司的測試資料庫驗證，驗證OK就可上線啦。

rogeryao iT邦超人 7 級 ‧ 2019-04-26 07:17:36 檢舉

為避免 A.整張取消與 B.指定單筆取消重複執行或交錯執行導致 test01.t01_02 及 test02.t02_02 重複加總,原程式加入

AND t03_04 <> 'Void'

登入發表回應

pinglam

iT邦新手 5 級 ‧ 2019-04-24 13:51:30

可以試一下先計算好再UPDATE,
set @sum_t03_03 = (select sum(t03_03) from test03 join test01 where t01_01 = t03_01);
UPDATE test01, test02, test03
SET
t01_02 = t01_02 + @sum_t03_03,
t02_02 = t02_02 + t03_03,
t03_04 = 'Void'
WHERE t01_01 = t03_01 AND t02_01 = t03_02;