iT邦幫忙

0

請教 SQL 指令的寫法(MySQL 環境)

先說明:小弟使用的資料庫環境是 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 }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
rogeryao
iT邦大師 1 級 ‧ 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

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

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

rogeryao iT邦大師 1 級 ‧ 2019-04-26 07:17:36 檢舉

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

AND t03_04 <> 'Void' 
0
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;

小魚 iT邦大師 1 級 ‧ 2019-04-24 14:02:45 檢舉

t02_02 應該也是要加sum...
畢竟這種東西有可能有很多筆...

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

感謝 pinglam 大大的回答
試了一下,在單筆(test01只有一筆要取消)時可用。
但是要批次處理多筆時(test01、test02雙方有多筆要取消)
會遇到問題。

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

雖是第一回遇到單邊(test01)有兩筆的情形
但小魚大大提醒的很對
應該要未雨綢繆,考慮兩邊都會遇到有兩筆的狀況。

我要發表回答

立即登入回答