本文介绍通过GBase 8a数据库，从学生每个学科的成绩，统计各系，各班级，不合格率的排行情况。
目录导航
需求环境
合格的判定
每个科目设定一个合格的最低标准，低于标准则为不合格。
学生(S)
8个学生，平均分散在2个系，4个班级里面。
create table s(Sno int ,Sname varchar(100),Ssex int,Sage int,Sdept varchar(100),Sclass varchar(100));
-- 表中属性分别为：学号、姓名、性别、年龄和系名。
insert into s values(1,'张1',1,18,'计算机系','计算机系1班');
insert into s values(2,'张2',1,18,'计算机系','计算机系1班');
insert into s values(3,'张3',1,18,'计算机系','计算机系2班');
insert into s values(4,'张4',1,18,'计算机系','计算机系2班');
insert into s values(5,'张5',1,18,'自动化系','自动化系1班');
insert into s values(6,'张6',1,18,'自动化系','自动化系1班');
insert into s values(7,'张7',1,18,'自动化系','自动化系2班');
insert into s values(8,'张8',1,18,'自动化系','自动化系2班');
gbase> select * from s;
+------+-------+------+------+--------------+------------------+
| Sno | Sname | Ssex | Sage | Sdept | Sclass |
+------+-------+------+------+--------------+------------------+
| 1 | 张1 | 1 | 18 | 计算机系 | 计算机系1班 |
| 2 | 张2 | 1 | 18 | 计算机系 | 计算机系1班 |
| 3 | 张3 | 1 | 18 | 计算机系 | 计算机系2班 |
| 4 | 张4 | 1 | 18 | 计算机系 | 计算机系2班 |
| 5 | 张5 | 1 | 18 | 自动化系 | 自动化系1班 |
| 6 | 张6 | 1 | 18 | 自动化系 | 自动化系1班 |
| 7 | 张7 | 1 | 18 | 自动化系 | 自动化系2班 |
| 8 | 张8 | 1 | 18 | 自动化系 | 自动化系2班 |
+------+-------+------+------+--------------+------------------+
8 rows in set (Elapsed: 00:00:00.00)科目(C)
就语文和数学两科
create table c(Cno int,Cname varchar(100),Cteacher varchar(100),Ccredit int);
-- 表中属性分别为：课号、课名、教师名和学分。
insert into c values(101,'语文','语文老师',80);
insert into c values(102,'数学','数学老师',70);
gbase> select * from c;
+------+--------+--------------+---------+
| Cno | Cname | Cteacher | Ccredit |
+------+--------+--------------+---------+
| 101 | 语文 | 语文老师 | 80 |
| 102 | 数学 | 数学老师 | 70 |
+------+--------+--------------+---------+
2 rows in set (Elapsed: 00:00:00.00)成绩(SC)
每组2人，
- 第一组，全部及格
- 第二组，1门不及格
- 第三组，2门不及格
- 第四组，3门不及格
create table sc(Sno int,Cno int,grade int);
-- 表中属性分别为：学号、课号、成绩和学分
insert into sc values(1,101,140);
insert into sc values(1,102,120);
insert into sc values(2,101,120);
insert into sc values(2,102,100);
insert into sc values(3,101,120);
insert into sc values(3,102,120);
insert into sc values(4,101,120);
insert into sc values(4,102,60);
insert into sc values(5,101,120);
insert into sc values(5,102,120);
insert into sc values(6,101,70);
insert into sc values(6,102,60);
insert into sc values(7,101,120);
insert into sc values(7,102,60);
insert into sc values(8,101,70);
insert into sc values(8,102,60);
gbase> select * from sc;
+------+------+-------+
| Sno | Cno | grade |
+------+------+-------+
| 1 | 101 | 140 |
| 1 | 102 | 120 |
| 2 | 101 | 120 |
| 2 | 102 | 100 |
| 3 | 101 | 120 |
| 3 | 102 | 120 |
| 4 | 101 | 120 |
| 4 | 102 | 60 |
| 5 | 101 | 120 |
| 5 | 102 | 120 |
| 6 | 101 | 70 |
| 6 | 102 | 60 |
| 7 | 101 | 120 |
| 7 | 102 | 60 |
| 8 | 101 | 70 |
| 8 | 102 | 60 |
+------+------+-------+
16 rows in set (Elapsed: 00:00:00.00)分析和运行结果
根据成绩,关联学科最低标准，计算是否及格
如果成绩 sc.grade 低于 c.ccredit 则认定不及格， 对应isFail=1
gbase> select sc.*,c.Ccredit,sc.grade<c.ccredit isFail from sc left join c on c. cno=sc.cno;
+------+------+-------+---------+--------+
| Sno | Cno | grade | Ccredit | isFail |
+------+------+-------+---------+--------+
| 1 | 101 | 140 | 80 | 0 |
| 1 | 102 | 120 | 70 | 0 |
| 2 | 101 | 120 | 80 | 0 |
| 2 | 102 | 100 | 70 | 0 |
| 3 | 101 | 120 | 80 | 0 |
| 3 | 102 | 120 | 70 | 0 |
| 4 | 101 | 120 | 80 | 0 |
| 4 | 102 | 60 | 70 | 1 |
| 5 | 101 | 120 | 80 | 0 |
| 5 | 102 | 120 | 70 | 0 |
| 6 | 101 | 70 | 80 | 1 |
| 6 | 102 | 60 | 70 | 1 |
| 7 | 101 | 120 | 80 | 0 |
| 7 | 102 | 60 | 70 | 1 |
| 8 | 101 | 70 | 80 | 1 |
| 8 | 102 | 60 | 70 | 1 |
+------+------+-------+---------+--------+
16 rows in set (Elapsed: 00:00:00.00)
拿到学生对应的系和班级信息
gbase> select sc.*,c.Ccredit,sc.grade<c.ccredit isFail,s.Sdept,s.Sclass from sc left join c on c.cno=sc.cno left join s on s.sno = sc.sno;
+------+------+-------+---------+--------+--------------+------------------+
| Sno | Cno | grade | Ccredit | isFail | Sdept | Sclass |
+------+------+-------+---------+--------+--------------+------------------+
| 1 | 101 | 140 | 80 | 0 | 计算机系 | 计算机系1班 |
| 1 | 102 | 120 | 70 | 0 | 计算机系 | 计算机系1班 |
| 2 | 101 | 120 | 80 | 0 | 计算机系 | 计算机系1班 |
| 2 | 102 | 100 | 70 | 0 | 计算机系 | 计算机系1班 |
| 3 | 101 | 120 | 80 | 0 | 计算机系 | 计算机系2班 |
| 3 | 102 | 120 | 70 | 0 | 计算机系 | 计算机系2班 |
| 4 | 101 | 120 | 80 | 0 | 计算机系 | 计算机系2班 |
| 4 | 102 | 60 | 70 | 1 | 计算机系 | 计算机系2班 |
| 5 | 101 | 120 | 80 | 0 | 自动化系 | 自动化系1班 |
| 5 | 102 | 120 | 70 | 0 | 自动化系 | 自动化系1班 |
| 6 | 101 | 70 | 80 | 1 | 自动化系 | 自动化系1班 |
| 6 | 102 | 60 | 70 | 1 | 自动化系 | 自动化系1班 |
| 7 | 101 | 120 | 80 | 0 | 自动化系 | 自动化系2班 |
| 7 | 102 | 60 | 70 | 1 | 自动化系 | 自动化系2班 |
| 8 | 101 | 70 | 80 | 1 | 自动化系 | 自动化系2班 |
| 8 | 102 | 60 | 70 | 1 | 自动化系 | 自动化系2班 |
+------+------+-------+---------+--------+--------------+------------------+
16 rows in set (Elapsed: 00:00:00.00)
根据系，班级，统计各自的不及格率
采用不合格的人数:sum(isFail) 除以总数 count(*)得到不及格率。
gbase> select sdept,sclass,sum(isFail)/count(*) failRate from ( select sc.*,c.Ccredit,sc.grade<c.ccredit isFail,s.Sdept,s.Sclass from sc left join c on c.cno=sc.cno left join s on s.sno = sc.sno) t group by sdept,sclass order by failrate desc;
+--------------+------------------+----------+
| sdept | sclass | failRate |
+--------------+------------------+----------+
| 自动化系 | 自动化系2班 | 0.7500 |
| 自动化系 | 自动化系1班 | 0.5000 |
| 计算机系 | 计算机系2班 | 0.2500 |
| 计算机系 | 计算机系1班 | 0.0000 |
+--------------+------------------+----------+
4 rows in set (Elapsed: 00:00:00.00)
及格率统计
可以用1减去不及格率，也可以从原始的判断大于等于及格线设置 isOK=1。
gbase> select sdept,sclass,sum(isOK)/count(*) okRate from ( select sc.*,c.Ccredit,sc.grade>=c.ccredit isOK,s.Sdept,s.Sclass from sc left join c on c.cno=sc.cno left join s on s.sno = sc.sno) t group by sdept,sclass order by okRate desc;
+--------------+------------------+--------+
| sdept | sclass | okRate |
+--------------+------------------+--------+
| 计算机系 | 计算机系1班 | 1.0000 |
| 计算机系 | 计算机系2班 | 0.7500 |
| 自动化系 | 自动化系1班 | 0.5000 |
| 自动化系 | 自动化系2班 | 0.2500 |
+--------------+------------------+--------+
4 rows in set (Elapsed: 00:00:00.01)