建站
咨詢
售后
建站咨詢
新聞中心
DROP TABLE IF EXISTS `t_user_evaluate`;
CREATE TABLE `t_user_evaluate` (
`id` int(25) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
`matron_id` int(25) NOT NULL COMMENT 'matron_id',
`order_id` int(25) NOT NULL COMMENT '訂單t_matron_order.id',
`file_id` int(10) DEFAULT NULL COMMENT '圖片id關(guān)聯(lián)t_system_files.id',
`evaluate_type` char(10) DEFAULT NULL COMMENT '1.寶寶護(hù)理 2 溝通技巧 3膳食搭配 4服務(wù)態(tài)度',
`score_grade` int(11) DEFAULT NULL COMMENT '技能評分（滿意等級，1：一星非常不滿意，2：二星不滿意:3：三星一般滿意，4：四星比較滿意，5：五星非常滿意）',
`synthesize_grade` int(11) DEFAULT NULL COMMENT '綜合評分（1：一星非常不滿意，2：二星不滿意:3：三星一般滿意，4：四星比較滿意，5：五星非常滿意）',
`phone` varchar(11) NOT NULL COMMENT '手機(jī)號碼,匿名時加星號存儲',
`context` varchar(200) DEFAULT NULL COMMENT '評價內(nèi)容',
`status` char(1) NOT NULL DEFAULT '0' COMMENT '狀態(tài)，0：未刪除，1：已刪除',
`create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
`update_id` varchar(25) DEFAULT '' COMMENT '修改人',
`update_time` datetime DEFAULT NULL COMMENT '更新時間',
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`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='評價表';
-- ----------------------------
-- Records of t_user_evaluate
-- ----------------------------
INSERT
INTO `t_user_evaluate` VALUES ('1', '1', '11', '10001', null, '1', '3',
'5', '111', null, '0', '2018-04-30 15:42:00', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('2', '1', '11', '10001', null, '2', '4', '5',
'111', null, '0', '2018-04-30 15:42:56', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('3', '1', '11', '10001', null, '3', '4', '5',
'111', null, '0', '2018-04-30 15:44:16', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('4', '1', '11', '10001', null, '4', '4', '5',
'111', null, '0', '2018-04-30 15:45:18', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('5', '1', '11', '10002', null, '1', '3', '4',
'111', null, '0', '2018-04-30 15:42:00', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('6', '1', '11', '10002', null, '2', '3', '4',
'111', null, '0', '2018-04-30 15:42:56', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('7', '1', '11', '10002', null, '3', '3', '4',
'111', null, '0', '2018-04-30 15:44:16', '', null);
INSERT INTO
`t_user_evaluate` VALUES ('8', '1', '11', '10002', null, '4', '3', '4',
'111', null, '0', '2018-04-30 15:45:18', '', null);
突泉網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)建站,突泉網(wǎng)站設(shè)計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為突泉千余家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)營銷網(wǎng)站建設(shè)要多少錢，請找那個售后服務(wù)好的突泉做網(wǎng)站的公司定做！
###################################################################
業(yè)務(wù)需求:根據(jù)評價類別（evaluate_type）計算技能評分（score_grade）平均分
#######################普通sql##############################################
SELECT AVG(t.score_grade) as A FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=1 ;
SELECT AVG(t.score_grade) as B FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=2 ;
SELECT AVG(t.score_grade) as C FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=3 ;
SELECT AVG(t.score_grade) as D FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=4 ;
######################使用union ALL####################
SELECT AVG(t.score_grade) as A FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=1
UNION ALL
SELECT AVG(t.score_grade) as B FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=2
UNION ALL
SELECT AVG(t.score_grade) as C FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=3
UNION ALL
SELECT AVG(t.score_grade) as D FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=4
#####################使用子查詢######################
SELECT DISTINCT
(SELECT AVG(t.score_grade) FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=1 )as a,
(SELECT AVG(t.score_grade) FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=2) as b,
(SELECT AVG(t.score_grade) FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=3 ) as c,
(SELECT AVG(t.score_grade) FROM `t_user_evaluate` t where t.matron_id=11 AND t.evaluate_type=4 ) as d
FROM `t_user_evaluate`
##################使用CASE WHEN 條件 THEN 值 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 `t_user_evaluate` t where t.matron_id=11 ;
分享標(biāo)題：casewhen
本文網(wǎng)址：http://fisionsoft.com.cn/article/pccped.html
