更新語句，涉及三個表，怎么寫呢

其實我也沒看懂，但我知道用SUM的話必定應(yīng)該用GROUP BY的。

把需要執(zhí)行的update語句分成每張表的update語句，然后放在事務(wù)里執(zhí)行，這樣應(yīng)該是最簡單的方法了吧

最簡單的是分三條一起執(zhí)行啊 加個begin end;就好了

看了你的問題，但不清楚你的業(yè)務(wù)表的具體表結(jié)構(gòu)，把重要的字段提取出來，作了個DEMO，不知道是不是你問題里面想要的結(jié)果（溫馨提示一下：下次提問的問題還是把你的表結(jié)構(gòu)和測試用例寫出來吧），以下是測試表結(jié)構(gòu)和數(shù)據(jù)：

CREATE DATABASE `test`;

USE `test`;

DROP TABLE IF EXISTS `sales_trade`;

CREATE TABLE `sales_trade` (
  `id` int(11) DEFAULT NULL,
  `trade_id` varchar(16) DEFAULT NULL,
  `trade_status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `sales_trade`(`id`,`trade_id`,`trade_status`) values 
(1,'order_num_001',30),
(2,'order_num_002',35);

DROP TABLE IF EXISTS `sales_trade_order`;

CREATE TABLE `sales_trade_order` (
  `id` int(11) DEFAULT NULL,
  `trade_id` varchar(16) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL,
  `actual_num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `sales_trade_order`(`id`,`trade_id`,`spec_id`,`actual_num`) values 
(1,'order_num_002',1,5),
(2,'order_num_003',3,10),
(3,'order_num_001',3,2);


DROP TABLE IF EXISTS `stock_spec`;

CREATE TABLE `stock_spec` (
  `id` int(11) DEFAULT NULL,
  `trade_id` varchar(16) DEFAULT NULL,
  `order_num` int(11) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `stock_spec`(`id`,`trade_id`,`order_num`,`spec_id`) values 
(1,'order_num_001',0,1),
(2,'order_num_002',0,1),
(3,'order_num_003',0,3);

以下是更新操作：

UPDATE stock_spec AS ss 
INNER JOIN (
    SELECT sto.spec_id,SUM(actual_num) AS total_num 
    FROM sales_trade_order AS sto 
    LEFT JOIN sales_trade AS st ON sto.trade_id=st.trade_id
    WHERE (st.trade_status=30 OR st.trade_status=35)
    GROUP BY sto.spec_id
) AS t
SET ss.order_num=t.total_num 
WHERE ss.spec_id=t.spec_id

Before updated:

After updated:

update stock_spec ss SET order_num=(
    SELECT SUM(actual_num) FROM sales_trade_order sto where sto.trade_id in (select trade_id from sales_trade st where (st.trade_status=30 or st.trade_status=35)) and sto.spec_id=ss.spec_id);