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;