[SQL]一道简单的sql题，某软开的笔试

CREATE TABLE `department`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `total_person_count` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, '科技', 3);
INSERT INTO `department` VALUES (2, '算法', 2);
INSERT INTO `department` VALUES (3, '运营', 2);
INSERT INTO `department` VALUES (4, '管理', 1);

表一 id name 和 total_person_count

CREATE TABLE `person`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `d_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

表二 id name 和d_id

题目1：

展示

部门名   人员总数

科技    3
算法    2
运营    2
管理    1

但是当时人员总是是空的，需要联查。

SELECT d.`name` as 部门名, p.num as n from `department` d JOIN (SELECT count(*) as num, d_id  FROM `person` GROUP BY  d_id) p on d.id = p.d_id

 

更新第一个第一个部门表每个部门的总人数：

UPDATE  `department` d SET d.total_person_count = (SELECT COUNT(1) FROM `person` p WHERE p.d_id = d.id)