有沒有辦法將選擇查詢存盤到表中？我通常使用 WITH 陳述句，但這僅適用于 mysql 8 。我目前正在使用 mysql 5.1。因此我嘗試實作一個臨時表，但在同一個查詢中不能多次參考臨時表（如下面的查詢）。
CREATE TEMPORARY TABLE test
SELECT TIMESTAMPDIFF(MONTH, MAX(L.period), CURDATE()) as timediff, L.contractID
FROM license L GROUP BY L.contractID;
UPDATE `contract` AS C
SET C.valid = CASE
WHEN (SELECT timediff FROM test AS T WHERE T.contractID = C.contractID) < 1 THEN 2
WHEN (SELECT timediff FROM test AS T WHERE T.contractID = C.contractID) = 1 THEN 1
WHEN (SELECT timediff FROM test AS T WHERE T.contractID = C.contractID) > 1 THEN 0
END;
DROP TEMPORARY TABLE test;
uj5u.com熱心網友回復：
您可以通過將更新表述為連接來立即解決您的問題：
UPDATE contract c
INNER JOIN test t
ON t.contractID = c.contractID
SET valid = CASE WHEN t.timediff < 1 THEN 2
WHEN t.timediff = 1 THEN 1
ELSE 0 END;
這種方法解決了多次參考臨時表的問題。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/406049.html
標籤：
上一篇：mySQL創建表唯一索引