本文共 1774 字，大约阅读时间需要 5 分钟。

表结构和数据

CREATE TABLE `score`  (  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,  `s_score` int(3) NULL DEFAULT NULL,  PRIMARY KEY (`s_id`, `c_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;INSERT INTO `score` VALUES ('01', '01', 80);INSERT INTO `score` VALUES ('01', '02', 90);INSERT INTO `score` VALUES ('01', '03', 99);INSERT INTO `score` VALUES ('02', '01', 70);INSERT INTO `score` VALUES ('02', '02', 60);INSERT INTO `score` VALUES ('02', '03', 80);INSERT INTO `score` VALUES ('03', '01', 80);INSERT INTO `score` VALUES ('03', '02', 80);INSERT INTO `score` VALUES ('03', '03', 80);INSERT INTO `score` VALUES ('04', '01', 50);INSERT INTO `score` VALUES ('04', '02', 30);INSERT INTO `score` VALUES ('04', '03', 20);INSERT INTO `score` VALUES ('05', '01', 76);INSERT INTO `score` VALUES ('05', '02', 87);INSERT INTO `score` VALUES ('06', '01', 31);INSERT INTO `score` VALUES ('06', '03', 34);INSERT INTO `score` VALUES ('07', '02', 89);INSERT INTO `score` VALUES ('07', '03', 98);INSERT INTO `score` VALUES ('1', '2', 300);INSERT INTO `score` VALUES ('1', '3', 300);INSERT INTO `score` VALUES ('1', '4', 300);INSERT INTO `score` VALUES ('1003', '4', null);INSERT INTO `score` VALUES ('1004', '3', null);

一、if else

SELECT	s_id,	s_score, --这个课可以不用查询出来下面的if else也能用IF	( s_score > 80, '优秀', '良好' ) AS 等级  -- 这个等级就是别名，也能起成s_scoreFROM	score

二、case when then

SELECT 	s_id,	s_score,--可以不用写，下面也能用		(CASE 					WHEN s_score>=100 THEN '优秀'					WHEN s_score>=80 THEN '良好'					WHEN s_score>=60 THEN '中等'					ELSE'良好'			END ) 等级-- 等级只是一个别名而已 FROM score

三、 IFNULL

SELECT	s_id,	IFNULL( s_score, '没有成绩' ) score FROM	score

转载地址：http://ipdfk.baihongyu.com/