MySql的case when then else end函数

一:case when函数有两种形式:

1：简单函数：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
格式:

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

2：搜索函数：CASE 搜索函数计算一组布尔表达式以确定结果。
格式:

 CASE WHEN [expr] THEN [result1]…ELSE [default] END

两种格式都支持可选的 ELSE 参数。

二:举例说明:

1:创建表: 学生表student 老师表teacher 课程表course 以及 成绩表score

!课程表](https://img-blog.csdnimg.cn/20200522111029227.png)


2:简单查询:在 SELECT 语句中，简单 CASE 函数仅检查是否相等，而不进行其它比较
**格式:CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

SELECT  
  case s_id
 WHEN 1 THEN 'haha'
 WHEN 22 THEN 'NV'
end  as s_id
FROM
student

3:搜索查询
A: 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 – (包括有成绩的和无成绩的)
//解析 case when 布尔表达式 then (true返回结果) else (false返回结果) end

select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score),2) end ) '平均成绩' from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL

B:查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select st.s_id,st.s_name,count(sc.c_id) "选课总数",sum(case when sc.s_score is null then 0 else sc.s_score end) '总成绩' 
from student st 
left join score sc on st.s_id = sc.s_id 
group by st.s_id

C:按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select st.s_id,st.s_name,
(case when avg(sc4.s_score) is null then 0 else avg(sc4.s_score) end) '平均分',
(case when sc.s_score is null then 0 else sc.s_score end) '语文',
(case when sc2.s_score is null then 0 else sc2.s_score end) '数学',
(case when sc3.s_score is null then 0 else sc3.s_score end) '英语' 
from student st
left join score sc  on sc.s_id=st.s_id  and sc.c_id='01'
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
left join score sc3 on sc3.s_id=st.s_id and sc3.c_id='03'
left join score sc4 on sc4.s_id=st.s_id
group by st.s_id 
order by avg(sc4.s_score) desc

D:查询学生的总成绩并进行排名

select st.s_id,st.s_name
,(case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end)
 from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id order by sum(sc.s_score) desc

E:、查询学生平均成绩及其名次 (自定义排名需要用一个函数@i:=@i+1)
// @i:=@i+1
Oracle中有一个伪列rownum，可以在生成查询结果表的时候生成一组递增的序列号。MySQL中没有这个伪列，但是有时候要用，可以用如下方法模拟生成一列自增序号。

-- @i=0 表示从一开始递增
set @i=0;  
-- @i:=@i+1 每次加1
select a.*,@i:=@i+1 from (
select st.s_id,st.s_name,round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end),2) '平均分' from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id order by sc.s_score desc) a

结果: