2021-06-18

case when语句的使用
一、
SELECT USER
.USERNAME,
USER.HEADPORTRAIT,
USER.NAME,
CASE WHEN SEX =1 THEN ‘男’
WHEN SEX =2 THEN ‘女’
ELSE ‘其他’
END AS SEX
FROM
sys_user USER
另一种写法：
SELECT USER
.USERNAME,
USER.HEADPORTRAIT,
USER.NAME,
CASE SEX WHEN 1 THEN ‘男’
WHEN 2 THEN ‘女’
ELSE ‘其他’
END AS SEX
FROM
sys_user USER

二、有一张表score，里面有3个字段：YW，SX，YY。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
select
case when YW >=80
then ‘优秀’
when YW>=60 then ‘及格’
else ‘不及格’ END AS ‘语文’,

case when SX >=80
then '优秀'
when SX>=60 then '及格'
else '不及格' END AS '数学',

case when YY >=80
then '优秀'
when YY>=60 then '及格'
else '不及格' END AS '英语' 
from score
DROP TABLE IF EXISTS `score`;

CREATE TABLE score (
YW int(50) NULL DEFAULT NULL,
SX int(50) NULL DEFAULT NULL,
YY int(50) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of score

---

INSERT INTO score VALUES (70, 100, 70);
INSERT INTO score VALUES (1, 80, 60);
INSERT INTO score VALUES (10, 10, 58);

SET FOREIGN_KEY_CHECKS = 1;