SQL Case When Then Else End 多条件判断
程序员文章站
2022-06-01 16:47:35
...
1.、简单CASE WHEN函数
CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END
2、CASE WHEN条件表达式函数
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
condition是一个返回布尔类型的表达式,如果表达式返回true,则整个函数返回相应result的值,如果表达式皆为false,则返回ElSE后result的值,如果省略了ELSE子句,则返回NULL
3、使用场景
- 场景1
有分数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
- 场景2
现老师要统计班中,有多少男同学,多少女同学,并统计男同学中有几人及格,女同学中有几人及格,要求用一个SQL输出结果。
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
- 场景3
ELECT
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
推荐阅读
-
SQL之case when then else end用法介绍
-
SQL中的case when then else end用法
-
SQL利用Case When Then多条件判断
-
sql学习之CASE WHEN THEN ELSE END的用法
-
sql语句case when then else end 返回一个符合条件的值
-
Oracle SQL nvl(),listagg() within group(),TO_CHAR(),CASE WHEN THEN ELSE END
-
sql查询各科成绩最高分、最低分和平均分(case when then else end)
-
SQL Server中使用判断语句(IF ELSE/CASE WHEN )案例
-
SQL之case when then else end用法介绍
-
SQL COUNT +Case When Then+IFNULL多条件判断实现多条件复杂统计