SQL语句使用09--------案例03（case when）

向右看齐 2022-06-08 11:27 167阅读 0赞

样本表  
(表的基本结构：  
Student(Sid,Sname,Sage,Ssex) 学生表  
Course(Cid,Cname,Tid) 课程表  
SC(Sid,Cid,score) 成绩表  
Teacher(Tid,Tname) 教师表  
)

**表在第一篇有完整SQL语句**

## 1.查询如下课程平均成绩和及格率的百分数(用”1行”显示): 数学（001），语文（002），英语 （003），化学（004） ##

这个可以分为两步来做：

1.  查询平均成绩；
2.  查询百分之
3.  组合

说起来应该思路很简单，但是这里会引入一个新函数，case-when

先看两条SQL语句

select *,(case ssex when '男' then '1'when '女' then '2' else '3' end)as a from student;
    select *,(case when ssex= '男' then '1'when ssex= '女' then '2' else '3' end)as a from student;

![这里写图片描述][SouthEast]

首先：这两条语句的效果是一样的，但是相对而言，后者在条件判断过程中更有优势，比如，我们还可以这样写：

select *,(case when ssex= '男' then '1'when sage> '60' then '2' else '3' end)as a from student;
    //大于60的我们就归类于2了，而第一种只是对ssex这一列进行归类

前一种通常被称为

#### **简单case函数** ####

对一个字段进行归类  
格式是：

case 字段 when 值 then 类别 when 值 then 类别 else 类 end

#### **搜索case函数** ####

可对多个字段归类  
格式：

case  when 字段=值 then 类别 when 字段=值 then 类别 else 类 end

那么继续总结，  
我们知道了case，那么我们是不是可以对归类的东西进行汇总了  
**我们可以将001的课程提取出来，再把课程数提出出来；**  
说的比较抽象，直接上SQL语句

select sum(case when cid ='001' then score else 0 end)as allscore from sc;
    select sum(case when cid ='001' then 1 else 0 end)as number from sc;

![这里写图片描述][SouthEast 1]

select sum(case when cid='001' then score else 0 end)/sum(case cid when '001' then 1 else 0 end )as 数学 from sc;

同理，可以得出其他几个；

select sum(case when cid='002' then score else 0 end)/sum(case cid when '002' then 1 else 0 end )as 语文 from sc;
       select sum(case when cid='003' then score else 0 end)/sum(case cid when '003' then 1 else 0 end )as 英语 from sc;
       select sum(case when cid='004' then score else 0 end)/sum(case cid when '004' then 1 else 0 end )as 化学 from sc;

![这里写图片描述][SouthEast 2]

说明，，，，比较偏理科~~

**然后就是百分数了**

select 100 * sum(case when Cid = '001' and score >= 60 then 1 else 0 end)/sum(case when Cid = '001' then 1 else 0 end) AS 数学 from sc; 
        select 100 * sum(case when Cid = '002' and score >= 60 then 1 else 0 end)/sum(case when Cid = '002' then 1 else 0 end) AS 语文 from sc; 
        select 100 * sum(case when Cid = '003' and score >= 60 then 1 else 0 end)/sum(case when Cid = '003' then 1 else 0 end) AS 英语 from sc; 
        select 100 * sum(case when Cid = '004' and score >= 60 then 1 else 0 end)/sum(case when Cid = '004' then 1 else 0 end) AS 化学 from sc;

![这里写图片描述][SouthEast 3]

百分数就不解释了，道理一样

最终结果

select sum(case when cid='001' then score else 0 end)/sum(case cid when '001' then 1 else 0 end )as 数学平均分 ,100 * sum(case when Cid = '001' and score >= 60 then 1 else 0 end)/sum(case when Cid = '001' then 1 else 0 end) AS 数学百分数 ,sum(case when cid='002' then score else 0 end)/sum(case cid when '002' then 1 else 0 end )as 语文数学平均分 ,sum(case when cid='003' then score else 0 end)/sum(case cid when '003' then 1 else 0 end )as 英语数学平均分 ,100 * sum(case when Cid = '003' and score >= 60 then 1 else 0 end)/sum(case when Cid = '003' then 1 else 0 end) AS 英语百分数 ,sum(case when cid='004' then score else 0 end)/sum(case cid when '004' then 1 else 0 end )as 化学数学平均分 ,100 * sum(case when Cid = '004' and score >= 60 then 1 else 0 end)/sum(case when Cid = '004' then 1 else 0 end) AS 化学百分数 from sc;

![这里写图片描述][SouthEast 4]

# 2.总结 #

case then很灵活也很有用，变化很多

[SouthEast]: /images/20220608/d2465f2ceedc44b9a05d875801ff3179.png
[SouthEast 1]: /images/20220608/91aefb23a2884b5d85f14e6861743bbb.png
[SouthEast 2]: /images/20220608/ceedf2c3d18f41bd8562e900adfbc913.png
[SouthEast 3]: /images/20220608/4b6f79aca83b46cfba6f5d65de379521.png
[SouthEast 4]: /images/20220608/025d1a92702241f39459c363b2a3aab5.png