mysql更新语句（根据a表和b表的关联关系，将b表中的计算结果更新到a表中）

需求

建表语句

DROP TABLE IF EXISTS `tbl_users`;
CREATE TABLE `tbl_users`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pay_total` bigint(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tbl_users` VALUES (1, '张三', 123960);
INSERT INTO `tbl_users` VALUES (2, '李四', 10000);
INSERT INTO `tbl_users` VALUES (3, '王五', NULL);

DROP TABLE IF EXISTS `tbl_users_pay_details`;
CREATE TABLE `tbl_users_pay_details`  (
  `id` int(11) NOT NULL,
  `time` datetime(0) NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `pay_amount` bigint(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `tbl_users_pay_details` VALUES (1, NULL, 1, 123400);
INSERT INTO `tbl_users_pay_details` VALUES (2, NULL, 1, 560);
INSERT INTO `tbl_users_pay_details` VALUES (3, NULL, 2, 10000);

解答

UPDATE tbl_users a
INNER JOIN ( 
	SELECT user_id, sum( pay_amount ) AS amount 
	FROM tbl_users_pay_details 
	GROUP BY user_id ) b ON a.id = b.user_id 
SET a.pay_total = b.amount;

或者

update tbl_users a,(
select 
 user_id,sum(pay_amount) as amount
from tbl_users_pay_details
group by user_id
)b
set a.pay_total=b.amount
where a.id=b.user_id

或者

UPDATE tbl_users a 
SET a.pay_total = ( SELECT sum( b.pay_amount ) FROM tbl_users_pay_details b WHERE a.id = b.user_id );