mysql数据库面试题(工作/成功率)

CREATE TABLE `t_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `job_type` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `begin_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of t_jobs
-- ----------------------------
INSERT INTO `t_jobs` VALUES ('1', '张三', '0', '1', '2020-10-29 23:57:12', '2020-10-30 00:22:33');
INSERT INTO `t_jobs` VALUES ('2', '李四', '1', '1', '2020-10-29 23:09:37', '2020-10-29 23:38:10');
INSERT INTO `t_jobs` VALUES ('3', '小明', '1', '0', '2020-10-29 23:14:02', '2020-10-29 23:29:11');
INSERT INTO `t_jobs` VALUES ('4', '小明', '2', '0', '2020-10-29 23:49:44', '2020-10-30 00:01:19');
INSERT INTO `t_jobs` VALUES ('5', '张三', '0', '0', '2020-10-30 00:17:28', '2020-10-30 00:18:29');
INSERT INTO `t_jobs` VALUES ('6', '李四', '1', '1', '2020-10-29 23:18:27', '2020-10-29 23:46:03');
INSERT INTO `t_jobs` VALUES ('7', '小明', '2', '1', '2020-10-29 23:17:52', '2020-10-30 00:13:05');
INSERT INTO `t_jobs` VALUES ('8', '李四', '1', '1', '2020-10-29 23:24:02', '2020-10-30 00:05:49');
INSERT INTO `t_jobs` VALUES ('9', '张三', '2', '1', '2020-10-29 22:51:29', '2020-10-29 23:16:16');
INSERT INTO `t_jobs` VALUES ('10', '李四', '0', '1', '2020-10-29 23:28:29', '2020-10-30 00:10:35');
INSERT INTO `t_jobs` VALUES ('11', '小明', '2', '1', '2020-10-29 22:50:05', '2020-10-29 23:05:38');
INSERT INTO `t_jobs` VALUES ('12', '小明', '2', '0', '2020-10-29 23:51:22', '2020-10-29 23:57:40');
INSERT INTO `t_jobs` VALUES ('13', '小明', '2', '1', '2020-10-29 23:34:23', '2020-10-29 23:59:16');
INSERT INTO `t_jobs` VALUES ('14', '李四', '0', '0', '2020-10-29 23:09:35', '2020-10-29 23:59:09');
INSERT INTO `t_jobs` VALUES ('15', '张三', '0', '0', '2020-10-29 23:34:36', '2020-10-29 23:56:22');
INSERT INTO `t_jobs` VALUES ('16', '小明', '1', '1', '2020-10-29 23:03:54', '2020-10-30 00:12:27');
INSERT INTO `t_jobs` VALUES ('17', '小明', '1', '1', '2020-10-29 23:09:04', '2020-10-29 23:34:19');
INSERT INTO `t_jobs` VALUES ('18', '张三', '1', '0', '2020-10-29 23:38:37', '2020-10-30 00:12:30');
INSERT INTO `t_jobs` VALUES ('19', '小小', '1', '1', '2020-10-14 22:12:51', '2020-10-14 22:12:53');
INSERT INTO `t_jobs` VALUES ('20', '小小 ', '1', '1', '2020-10-14 22:13:09', '2020-10-14 22:13:11');

# 第一题:
select COUNT(*), SUM(end_time-begin_time), SUM(`status`)/COUNT(`status`) from t_jobs where user_name = "张三";

# 第二题
select user_name, COUNT(*), SUM(end_time-begin_time), SUM(`status`)/COUNT(`status`) from t_jobs GROUP BY user_name;

# 第三题
SELECT
	job_type,
	COUNT(*),
	SUM(`status`)/COUNT(`STATUS`) as "成功比例",
	SUM(`status`) as "成功任务数量",
  	COUNT(`status`) - SUM(`status`) as "失败任务数量",
	avg(end_time-begin_time) as "平均耗时",
	max(end_time-begin_time) as "最大耗时",
	min(end_time-begin_time) as "最小耗时"
FROM
	t_jobs
GROUP BY
	job_type

# 第四题
# 这里总感觉写的有些不合理, 但是又不知道该怎么写, 之前也有想过三级分组,根据status再分组, 聚合分析的时候就不用考虑条件了
# 如果您有其他方案或者写法请留言, 不胜感激...
SELECT
	user_name,
	job_type,
	COUNT(*),
	SUM(`status`)/COUNT(`STATUS`) as "成功比例",
	SUM(`status`) as "成功任务数量1",
  sum(casE when `status` = 1  THEN end_time-begin_time ELSE 0 END ) as "成功总耗时",
  max(casE when `status` = 1  THEN end_time-begin_time ELSE 0 END ) as "成功最大耗时",
  min(casE when `status` = 1  THEN end_time-begin_time  END ) as "成攻最小耗时",
  avg(casE when `status` = 1  THEN end_time-begin_time  END ) as "成功平均耗时",

  sum(casE when `status` = 0  THEN end_time-begin_time ELSE 0 END ) as "失败总耗时",
  max(casE when `status` = 0  THEN end_time-begin_time ELSE 0 END ) as "失败最大耗时",
  min(casE when `status` = 0  THEN end_time-begin_time  END ) as "失败最小耗时",
  avg(casE when `status` = 0  THEN end_time-begin_time  END ) as "失败平均耗时"
FROM
	t_jobs
GROUP BY
	user_name,
	job_type