SQL中IF表达式
基本用法
if(expr1,expr2,expr3)
表达式expr1若是值为true,返回值是expr2;
表达式expr2若是值为false,返回值expr3;
案例说明
SELECT
stu_no, IF(score_prize >= 60, 'T', 'F') score_status
FROM
score;
IFNULL
基本用法
ifnull(expr1,expr2)
在expr1表达式值不为null情况下都返回expr1,否则返回expr2
案例说明
select ifnull(stu_name,'null') from student ;
case when使用
简单CASE WHEN函数只能应对一些简单的业务场景,而CASE WHEN条件表达式的写法则更加灵活。
基本用法
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
condition是一个返回布尔类型的表达式,如果表达式返回true,则整个函数返回相应result的值,如果表达式皆为false,则返回ElSE后result的值,如果省略了ELSE子句,则返回NULL。
案例说明
有分数score,score<60返回不及格,score>=60返回及格,score>=80返回优秀
SELECT
STUDENT_NAME,
(CASE WHEN score < 60 THEN '不及格'
WHEN score >= 60 AND score < 80 THEN '及格'
WHEN score >= 80 THEN '优秀'
ELSE '异常' END) AS REMARK
FROM
TABLE
注意:如果你想判断score是否null的情况,WHEN score = null THEN ‘缺席考试’,这是一种错误的写法,正确的写法应为:
CASE WHEN score IS NULL THEN ‘缺席考试’ ELSE ‘正常’ END
SELECT
E_CODE,
SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM
THTF_ENERGY_TEST
GROUP BY
E_CODE
SELECT
SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM
THTF_STUDENTS
CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)
博客来源