Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.