SQL题（user，car，pet）

 

CREATE TABLE `user`  (
  `userid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `weixin` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `income` int(255) NULL DEFAULT NULL,
  `marry` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `character` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `vip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`userid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES ('user001', '小赵', '男', 30, '135xxx', NULL, 800000, '是', '外向活泼', '是');
INSERT INTO `user` VALUES ('user002', '小钱', '女', 25, NULL, 'qhaha', 299800, '否', '泼辣', '是');
INSERT INTO `user` VALUES ('user003', '小孙', '男', 180, '188xxx', 'S1233', 49400, '是', '只有照片了', '否');
INSERT INTO `user` VALUES ('user004', '小李', '男', 55, '185xxx', NULL, 595000, '否', '帅哥一名', '是');
INSERT INTO `user` VALUES ('user005', '小周', '女', 33, '186xxx', 'Z666', 120000, '否', '气质优雅', '否');


CREATE TABLE `car`  (
  `carid` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `userid` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `car` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `license` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `totalvalue` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`carid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `car` VALUES ('car001', 'user001', '沃尔沃', '京N880066', '400000');
INSERT INTO `car` VALUES ('car002', 'user001', '奔驰', '辽A99999', '1000000');
INSERT INTO `car` VALUES ('car003', 'user004', '帕萨特', '京P77766', '300000');


CREATE TABLE `pet`  (
  `petid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `userid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pet` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `color` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`petid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `pet` VALUES ('pet001', 'user002', 'dog', 'white', '2000');
INSERT INTO `pet` VALUES ('pet002', 'user003', 'bird', 'red', '6000');
INSERT INTO `pet` VALUES ('pet003', 'user004', 'monkey', 'black', '50000');

答案：

###### 111
SELECT a.license,count(b.petid) FROM car a LEFT JOIN pet b ON a.userid = b.userid GROUP BY a.license ORDER BY count(b.petid)

###### 222
SELECT a.gender,count(b.gender) FROM (
    SELECT gender FROM user GROUP BY gender
) a LEFT JOIN 
(
    SELECT gender FROM user WHERE income < 100000 
) b ON a.gender = b.gender
GROUP BY a.gender 

###### 333
SELECT a.userid,a.income-b.sub_money FROM user a INNER JOIN (
    SELECT userid,price*0.1 as sub_money FROM pet 
) b ON a.userid = b.userid

UPDATE user a INNER JOIN (
    SELECT userid,price*0.1 as sub_money FROM pet 
) b ON a.userid = b.userid
SET a.income = a.income-b.sub_money