从一张表统计数据插入到另一张表（面试遇到的）

1、请直接看题目：

2、没有遇到过这个，刚开始是不会的！

以下是建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.1.103
 Source Server Type    : MySQL
 Source Server Version : 80017
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80017
 File Encoding         : 65001

 Date: 23/07/2020 19:06:32
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sale
-- ----------------------------
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sale_date` date NULL DEFAULT NULL,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sale_num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sale
-- ----------------------------
INSERT INTO `sale` VALUES (1, '2020-07-23', 'A', 10);
INSERT INTO `sale` VALUES (2, '2020-07-23', 'B', 21);
INSERT INTO `sale` VALUES (3, '2020-07-15', 'C', 10);
INSERT INTO `sale` VALUES (4, '2020-06-17', 'C', 15);
INSERT INTO `sale` VALUES (5, '2020-07-18', 'B', 13);
INSERT INTO `sale` VALUES (6, '2020-07-20', 'B', 3);
INSERT INTO `sale` VALUES (7, '2020-07-23', 'C', 9);
INSERT INTO `sale` VALUES (8, '2020-07-23', 'A', 2);

-- ----------------------------
-- Table structure for stock
-- ----------------------------
DROP TABLE IF EXISTS `stock`;
CREATE TABLE `stock`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `ymonth` date NULL DEFAULT NULL COMMENT '年月',
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品名称',
  `start_num` int(11) NULL DEFAULT NULL COMMENT '初期数量',
  `now_num` int(11) NULL DEFAULT NULL COMMENT '当前销售数量',
  `end_num` int(11) NULL DEFAULT NULL COMMENT '期末数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of stock
-- ----------------------------
INSERT INTO `stock` VALUES (1, '2020-07-23', 'A', 19, 12, 7);
INSERT INTO `stock` VALUES (2, '2020-07-23', 'B', 40, 37, 3);
INSERT INTO `stock` VALUES (3, '2020-07-23', 'C', 54, 19, 35);

SET FOREIGN_KEY_CHECKS = 1;

3、思路：先用group by 聚合作为子查询，update table join 子查询

4、实现：

UPDATE stock s
JOIN ( SELECT sname, sum( sale_num ) AS su FROM sale WHERE MONTH ( sale_date ) = 7 GROUP BY sname ) n ON s.sname = n.sname 
SET s.now_num = n.su,
s.end_num = s.start_num - n.su

5、未执行sql语句前：

执行更新后：

6、结语：其实不用算都知道是对的，难道你怀疑自己写的sql吗？哈哈~~