基础数据表结构Student
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
基础测试数据:
第一种:
判断范围(相当于IF语句)
CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2...] [ELSE vn] END
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT id,NAME,(CASE WHEN age>90 THEN '长寿老人'
WHEN age>60 THEN '老年'
WHEN age>45 THEN '中年'
WHEN age>18 THEN '青年'
ELSE '少年' END) AS sgeCase
FROM student;
第二种:
等值判断(相当于Switch)
CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2...] [ELSE vn] END
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT NAME,(CASE age WHEN 40 THEN '中年'
WHEN 30 THEN '青年'
WHEN 20 THEN '少年'
ELSE '未知年龄' END) AS sgeCase
FROM student;