南大通用GBase 8a SQL学习:统计各系、班级学生各科成绩不合格率,合格率排行的方法

本文介绍通过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)