CASE 字段 WHEN 预期值 THEN 结果1 ELSE 结果2 END
SELECT name,(CASE sex WHEN 0 THEN '女' ELSE '男' END) sex FROM score
CASE WHEN condition THEN result1 ELSE result2 END
SELECT name,score,(CASE WHEN score>=90 THEN '优秀' WHEN score>=80 THEN '良好' WHEN score>=60 THEN '及格' ELSE '不及格' END) level FROM score
SELECT SUM(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS 女生人数, SUM(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS 男生人数, SUM(CASE WHEN score>=60 AND sex=0 THEN 1 ELSE 0 END) 男生及格人数, SUM(CASE WHEN score>=60 AND sex=1 THEN 1 ELSE 0 END) 女生及格人数 FROM score;
SELECT name, MAX(CASE course WHEN '语文' THEN score ELSE 0 END) AS '语文', max(CASE course WHEN '数学' THEN score ELSE 0 END) AS '数学', max(CASE course WHEN '英语' THEN score ELSE 0 END) AS '英语', AVG(score) AS '平均成绩' FROM score GROUP BY name;
IF(expr,result_true,result_false)
SELECT name,IF(sex=1,'男','女')sex FROM students;
SELECT COUNT(IF(sex=1,1,NULL)) 男生人数,COUNT(IF(sex=0,1,NULL))女生人数 FROM students
SELECT price FROM goods WHERE name='light';
SELECT IFNULL(price,0) price FROM goods WHERE name='light';
-- 返回结果:nullSELECT IFNULL(price,0) price FROM goods WHERE name='aaa';12
-- 返回结果:null SELECT IFNULL(price,0) price FROM goods WHERE name='aaa';
-- 返回结果:0 SELECT IFNULL(SUM(price),0) FROM goods WHERE status=3;