Case when
DROP TABLE IF EXISTS `troomuser _ roomate`; CREATE TABLE `troomuser _ roomate` (`id`int (25) NOT NULL AUTO_INCREMENT COMMENT 'key ID', `matron_ id` int (25) NOT NULL COMMENT' matron_id', `order_ id` int (25) NOT NULL COMMENT 'order tweematronSecretorder.idwitness, `file_ id` int (10) DEFAULT NULL COMMENT' picture id associated with troomsystemroomfiles.idcards, `evaluate_ type` char (10) DEFAULT NULL COMMENT'1. Baby nursing 2 communication skills 3 meal matching 4 service attitude', 'score_ grade` int (11) DEFAULT NULL COMMENT' skill score (satisfaction level, 1: one star is very dissatisfied, 2: two stars are not satisfied: 3: three stars are generally satisfied, 4: four stars are satisfied, 5: five stars are very satisfied)', `synthesize_ grade` int (11) DEFAULT NULL COMMENT 'comprehensive score (1: one star is very dissatisfied 2: two stars are not satisfied: 3: three stars are generally satisfied, 4: four stars are satisfied, 5: five stars are very satisfied)', `phone`varchar (11) NOT NULL COMMENT 'mobile phone number, store anonymously with an asterisk', `context`varchar 'DEFAULT NULL COMMENT' evaluation content', `status`char (1) NOT NULL DEFAULT'0' COMMENT 'status, 0: not deleted 1: deleted', 'create_ time` datetime NOT NULL COMMENT' creation time', 'update_ id` varchar (25) DEFAULT' 'COMMENT' modifier', `update_ time`datetime DEFAULT NULL COMMENT 'update time', PRIMARY KEY (`id`), KEY `idx_u_user_ id` (`user_ id`) USING BTREE, KEY `idx_um_matron_ id` (`matron_ id`) USING BTREE, KEY `idx_o_order_ id` (`order_ id`) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT=' evaluation form' -Records of tweak username roommate VALUES-INSERT INTO `tresume username _ roomate` VALUES ('1,'1,'11, '10001, null,' 1, 3, 5, 111, null,'0' '2018-04-30 15-42-12-00,' null) INSERT INTO `tregistered username _ roomate`VALUES ('2,'1,'11, '10001, null,' 2,'4,'5, '111, null,' 0), '2018-04-30 15 null,', null) INSERT INTO `tkeeper username _ roomate`VALUES ('3,'1,'11, '10001, null,' 3,'4,'5, '111, null,' 0), '2018-04-30 15 null 44 16,', null) INSERT INTO `tregistered username _ roomate`VALUES ('44th,' 14th, '114th,' 10001th, null, '44th,' 44th, '54th,' 111mm, null, '04th,' 2018-04-30 1545 null 18mm,', null) INSERT INTO `tvendor username _ roomate`VALUES ('5mm,' 1mm, '11mm,' 10002mm, null, '1mm,' 3mm, '4cycles,' 111mm, null, '04th,' 2018-04-30 1515 null 42null, 'null) INSERT INTO `tvendor username _ roomate`VALUES ('6,'1,'11, '10002, null,' 2),'3,'4, '111, null,' 0, '2018-04-30 15 null,', null) INSERT INTO `tkeeper username _ roomate`VALUES ('7,'1,'11, '10002, null,' 3,'3,'3, 4, '111, null,' 0), '2018-04-30 15 null 44 16,', null) INSERT INTO `tkeeper username _ roomate`VALUES ('8,'1,'11, '10002, null,' 4),'3,'4, '111, null,' 0), '2018-04-30 15 null 45 null)
#
Business needs: calculate the average skill score (score_grade) based on the evaluation category (evaluate_type)
# ordinary sql#
SELECT AVG (t.score_grade) as A FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=1
SELECT AVG (t.score_grade) as B FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=2
SELECT AVG (t.score_grade) as C FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=3
SELECT AVG (t.score_grade) as D FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=4
# # using union ALL#
SELECT AVG (t.score_grade) as A FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=1
UNION ALL
SELECT AVG (t.score_grade) as B FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=2
UNION ALL
SELECT AVG (t.score_grade) as C FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=3
UNION ALL
SELECT AVG (t.score_grade) as D FROM `troomuser _ roomate`t where t.matron_id=11 AND t.evaluate_type=4
# use subquery # #
SELECT DISTINCT
(SELECT AVG (t.score_grade) FROM `troomUser`t where t.matron_id=11 AND t.evaluate_type=1) as a
(SELECT AVG (t.score_grade) FROM `troomUser`t where t.matron_id=11 AND t.evaluate_type=2) as b
(SELECT AVG (t.score_grade) FROM `troomUser`t where t.matron_id=11 AND t.evaluate_type=3) as c
(SELECT AVG (t.score_grade) FROM `troomUser`t where t.matron_id=11 AND t.evaluate_type=4) as d
FROM `troomusername _ roomate`
# use CASE WHEN conditional THEN value END #
SELECT AVG (CASE WHEN t.evaluate_type=1 THEN t.score_grade end) as a
AVG (CASE WHEN t.evaluate_type=2 THEN t.score_grade end) as b
AVG (CASE WHEN t.evaluate_type=3 THEN t.score_grade end) as c
AVG (CASE WHEN t.evaluate_type=4 THEN t.score_grade end) as d
FROM `troomuser _ roomate`t where t.matron_id=11
