样本表
(表的基本结构:
Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,score) 成绩表
Teacher(Tid,Tname) 教师表
)
表在第一篇有完整SQL语句
1.查询如下课程平均成绩和及格率的百分数(用”1行”显示): 数学(001),语文(002),英语 (003),化学(004)
这个可以分为两步来做:
- 查询平均成绩;
- 查询百分之
- 组合
说起来应该思路很简单,但是这里会引入一个新函数,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;
首先:这两条语句的效果是一样的,但是相对而言,后者在条件判断过程中更有优势,比如,我们还可以这样写:
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;
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;
说明,,,,比较偏理科~~
然后就是百分数了
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;
百分数就不解释了,道理一样
最终结果
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;
2.总结
case then很灵活也很有用,变化很多