用SQl来做学生成绩分析

mps777

在数据透视表区，学了一个多月的SQL，在此非常感谢香帅，魂断蓝桥等老师，现小麦将学习到的一点知识运用到学生成绩分析上，由于初学，所以有些语句可能比较长，请见谅。
因小麦的工作性质所限，对教育界的成绩分析不甚理解，只做了一些在论坛上遇到过的问题的有关情况，故此，请大家见谅：
下面大致分为：
1、一个班的成绩分析---------------------------------------------------------------------------2楼
2、全年级多班（初中）的成绩合并分析-----------------------------------------------------3楼
3、中职类的由期中、期末及平时成绩所求的总评成绩的分析----------------------------4楼
4、同一个文件夹里，引用各班的成绩的分析（只是合并）-------------------------------5楼
5、动态选择班别、科目得到所要的成绩表--------------------------------------------------6楼（值得一看）
新增
6、非子查询的各科排名（解决用子查询运行速度慢的做法）----------------------------14楼
7、请看香帅版主用非子查询来做各科排名--------------------------------------------------90楼（值得一看）
http://club.excelhome.net/forum.php?mod=redirect&goto=findpost&ptid=1012309&pid=6902164

mps777

1、一个班的成绩分析

数据源：


一、各科前三名（按成绩降序排序）

代码：

1. 
   
2. select 姓名,科目,成绩 from
   
3. (select 姓名,'语文' as 科目,语文 as 成绩,语文*100000 as 成绩2 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 语文 desc) union all
   
4. select 姓名,'数学',数学,数学*10000 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 数学 desc) union all
   
5. select 姓名,'英语',英语,英语*1000 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 英语 desc) union all
   
6. select 姓名,'物理',物理,物理*100 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 物理 desc) union all
   
7. select 姓名,'化学',化学,化学*10 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 化学 desc) union all
   
8. select 姓名,'生物',生物,生物 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 生物 desc) union all
   
9. select 姓名,'总分',总分,总分*10^6 as 成绩 from [成绩表$] where 学号 in (select top 3 学号 from [成绩表$] order by 总分 desc)) order by 成绩2 desc

复制代码

一、各科优秀率、平均成绩、最高分、最低分

代码：
优秀率

1. select
   
2. sum(iif(语文>=85,1))/count(语文) as 语文优秀率, sum(iif(数学>=85,1))/count(数学) as 数学优秀率, sum(iif(英语>=85,1))/count(英语) as 英语优秀率,
   
3. sum(iif(物理>=85,1))/count(物理) as 物理优秀率, sum(iif(化学>=85,1))/count(化学) as 化学优秀率, sum(iif(生物>=85,1))/count(生物) as 生物优秀率
   
4. from [成绩表$]

复制代码

平均分

1. select avg(语文) as 语文平均分, avg(数学) as 数学平均分, avg(英语) as 英语平均分, avg(物理) as 物理平均分,
   
2. avg(化学) as 化学平均分, avg(生物) as 生物平均分 from [成绩表$]

复制代码

最高分

1. 
   
2. select max(语文) as 语文最高分, max(数学) as 数学最高分, max(英语) as 英语最高分, max(物理) as 物理最高分, max(化学) as 化学最高分, max(生物) as 生物最高分 from [成绩表$]
   

复制代码

最低分

1. 
   
2. select min(语文) as 语文最低分, min(数学) as 数学最低分, min(英语) as 英语最低分, min(物理) as 物理最低分, min(化学) as 化学最低分, min(生物) as 生物最低分 from [成绩表$]
   

复制代码

一、各科成绩排名

代码：

1. 
   
2. select b.班别,b.学号,b.姓名,
   
3. 语文,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.语文>b.语文) as 语文排名,
   
4. 数学,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.数学>b.数学) as 数学排名,
   
5. 英语,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.英语>b.英语) as 英语排名,
   
6. 物理,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.物理>b.物理) as 物理排名,
   
7. 化学,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.化学>b.化学) as 化学排名,
   
8. 生物,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.生物>b.生物) as 生物排名,
   
9. 总分,1+(select count(1) from [成绩表$]a where a.班别=b.班别 and a.总分>b.总分) as 总分排名
   
10. from [成绩表$]b

复制代码

一、各科不合格人数

代码：

1. 
   
2. select
   
3. sum(iif(语文<60,1)) as 语文（人）,
   
4. sum(iif(数学<60,1)) as 数学（人）,
   
5. sum(iif(英语<60,1)) as 英语（人）,
   
6. sum(iif(物理<60,1)) as 物理（人）,
   
7. sum(iif(化学<60,1)) as 化学（人）,
   
8. sum(iif(生物<60,1)) as 生物（人）
   
9. from [成绩表$]

复制代码

经香帅版主指点，求各科不合格人数可以用以下代码：

1. select
   
2. -sum(语文<60) as 语文（人）,
   
3. -sum(数学<60) as 数学（人）,
   
4. -sum(英语<60) as 英语（人）,
   
5. -sum(物理<60) as 物理（人）,
   
6. -sum(化学<60) as 化学（人）,
   
7. -sum(生物<60) as 生物（人）
   
8. from [成绩表$]

复制代码

一、各科补考名单
   以姓名为序                       以科目为序                各人补考科目
这里的各人补考科目是利用转置方式来做的，数据源是用“以姓名为序”图示的数据。要学转置请看香帅精华帖（http://club.excelhome.net/thread-814059-1-1.html）
代码：
以姓名为序

1. 
   
2. select 姓名,补考科目 from
   
3. (select '语文' as 补考科目,姓名,学号 from [成绩表$] where 语文<60 union all
   
4. select '数学',姓名,学号 from [成绩表$] where 数学<60 union all
   
5. select '英语',姓名,学号 from [成绩表$] where 英语<60 union all
   
6. select '物理',姓名,学号 from [成绩表$] where 物理<60 union all
   
7. select '化学',姓名,学号 from [成绩表$] where 化学<60 union all
   
8. select '生物',姓名,学号 from [成绩表$] where 生物<60) order by  学号

复制代码

各人补考科目（转置）

1. transform last(姓名)
   
2. select null from
   
3. (select 姓名 as 姓名,0 as 列,dsum("1","各科补考名单$","姓名<='"&姓名&"'") as 行 from [各科补考名单$]
   
4. union all
   
5. select 补考科目,dsum("1","各科补考名单$","姓名='"&姓名&"' and 补考科目<='"&补考科目&"'")+0.1 as 列,dsum("1","各科补考名单$","姓名<='"&姓名&"'") as 行 from [各科补考名单$] )
   
6. group by 行
   
7. pivot 列

复制代码

一、三好学生

1. select 学号,姓名 from [成绩表$] where (语文>=85)+(数学>=85)+(英语>=85)+(物理>=85)+(化学>=85)+(生物>=85)=-6
   

复制代码

我的数据表与代码
一个班数据.zip (39.58 KB, 下载次数: 1620)

2013-4-28 23:28 上传

点击文件名下载附件

                  一个班代码.zip (1.25 KB, 下载次数: 1172)

2013-4-28 23:28 上传

点击文件名下载附件

mps777

2、全年级多班（初中）的成绩合并分析
二、数据源
初一1班 初一2班
初一3班 任课表
二、三班汇总（全级）

代码：

1. 
   
2. select * from [初一1$]
   
3. union all
   
4. select * from [初一2$]
   
5. union all
   
6. select * from [初一3$]

复制代码

二、全级总分前二十名（1：不含名次；2含名次）
不含名次 含名次
代码：
不含次：

1. 
   
2. select top 20 班别,姓名,总分 from
   
3. (select * from [初一1$]
   
4. union all
   
5. select * from [初一2$]
   
6. union all
   
7. select * from [初一3$])
   
8. order by 3 desc

复制代码

含名次代码：

1. 
   
2. select t2.班别,t2.姓名,t2.总分,(select count(1)+1 from
   
3. (select top 20 初一,班别,姓名,总分 from
   
4. (select '初一' as 初一,* from [初一1$]
   
5. union all
   
6. select '初一',* from [初一2$]
   
7. union all
   
8. select '初一',* from [初一3$]) order by 4 desc)t1  where t1.初一=t2.初一 and t1.总分>t2.总分) as 名次 from
   
9. (select top 20 初一,班别,姓名,总分 from
   
10. (select '初一' as 初一,* from [初一1$]
    
11. union all
    
12. select '初一',* from [初一2$]
    
13. union all
    
14. select '初一',* from [初一3$]) order by 4 desc)t2
    

复制代码

二、各科班级排名、年级排名

这个用子查询来完成，有点慢。因为都要三表汇总，所以以下的所有分析都是直接取“三班汇总”表数据源来做，目的便于读者阅读。
代码：

1. 
   
2. select t2.班别,t2.姓名,
   
3. t2.语文,
   
4. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.班别=t2.班别 and t1.语文>t2.语文) as 语文班排名,
   
5. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.一=t2.一 and t1.语文>t2.语文) as 语文级排名,
   
6. t2.数学,
   
7. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.班别=t2.班别 and t1.数学>t2.数学) as 数学班排名,
   
8. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.一=t2.一 and t1.数学>t2.数学) as 数学级排名,
   
9. t2.英语,
   
10. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.班别=t2.班别 and t1.英语>t2.英语) as 英语班排名,
    
11. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.一=t2.一 and t1.英语>t2.英语) as 英语级排名,
    
12. t2.政治,
    
13. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.班别=t2.班别 and t1.政治>t2.政治) as 政治班排名,
    
14. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.一=t2.一 and t1.政治>t2.政治) as 政治级排名,
    
15. t2.总分,
    
16. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.班别=t2.班别 and t1.总分>t2.总分) as 总分班排名,
    
17. (select count(1)+1 from (select '一' as 一,* from [三班汇总$])t1 where t1.一=t2.一 and t1.总分>t2.总分) as 总分级排名  from  
    
18. (select '一' as 一,* from [三班汇总$])t2

复制代码

二、各科全级前十名

代码：

1. 
   
2. select 班别,姓名,科目,成绩 from
   
3. (select 班别,姓名,语文 as 成绩,语文*10^3 as 成绩2,'语文' as 科目 from [三班汇总$] where 姓名 in (select top 10 姓名 from [三班汇总$] order by 语文 desc )
   
4. union all
   
5. select 班别,姓名,数学,数学*100,'数学' from [三班汇总$] where 姓名 in (select top 10 姓名 from [三班汇总$] order by 数学 desc )
   
6. union all
   
7. select 班别,姓名,英语,英语,'英语' from [三班汇总$] where 姓名 in (select top 10 姓名 from [三班汇总$] order by 英语 desc )
   
8. union all
   
9. select 班别,姓名,政治,政治,'政治' from [三班汇总$] where 姓名 in (select top 10 姓名 from [三班汇总$] order by 政治 desc )
   
10. union all
    
11. select 班别,姓名,总分,总分*10^4,'总分' from [三班汇总$] where 姓名 in (select top 10 姓名 from [三班汇总$] order by 总分 desc )) order by 成绩2 desc

复制代码

二、各课任老师班级优秀率

1、各教师各班优秀率代码：

1. 
   
2. select 授课老师,班别,
   
3. sum(iif(成绩>=85,1,0)) as 优秀人数,count(*) as 班人数,sum(iif(成绩>=85,1,0))/count(*) as 优秀率 from
   
4. (select t1.*,t2.授课老师 from
   
5. (select 班别,语文 as 成绩,'语文' as 科目 from [三班汇总$]
   
6. union all
   
7. select 班别,数学,'数学' from [三班汇总$]
   
8. union all
   
9. select 班别,英语,'英语' from [三班汇总$]
   
10. union all
    
11. select 班别,政治,'政治' from [三班汇总$] )t1 left join [任课表$]t2
    
12. on t1.班别=t2.班别 and t1.科目=t2.科目) group by 班别,授课老师 order by 1

复制代码

2、各教师优秀率

1. select 授课老师,
   
2. sum(iif(成绩>=85,1,0)) as 优秀人数,count(*) as 班人数,sum(iif(成绩>=85,1,0))/count(*) as 优秀率 from
   
3. (select t1.*,t2.授课老师 from
   
4. (select 班别,语文 as 成绩,'语文' as 科目 from [三班汇总$]
   
5. union all
   
6. select 班别,数学,'数学' from [三班汇总$]
   
7. union all
   
8. select 班别,英语,'英语' from [三班汇总$]
   
9. union all
   
10. select 班别,政治,'政治' from [三班汇总$] )t1 left join [任课表$]t2
    
11. on t1.班别=t2.班别 and t1.科目=t2.科目) group by 授课老师 order by 1

复制代码

二、各分数段人数
这里是用SQL+数据透视表+分组来完成的……

代码：

1. 
   
2. select 班别,姓名,语文 as 成绩,'语文' as 科目 from [三班汇总$]
   
3. union all
   
4. select 班别,姓名,数学,'数学' from [三班汇总$]
   
5. union all
   
6. select 班别,姓名,英语,'英语' from [三班汇总$]
   
7. union all
   
8. select 班别,姓名,政治,'政治' from [三班汇总$]

复制代码

二、全级总分前50名各班人数

代码：

1. 
   
2. select 班别,count(*) as 人数 from [三班汇总$] where 姓名 in (select top 50 姓名 from [三班汇总$] order by 总分 desc) group by 班别

复制代码

我的数据表与代码
多个班数据.zip (62.68 KB, 下载次数: 881)

2013-4-28 23:34 上传

点击文件名下载附件

                         多个班代码.zip (1015 Bytes, 下载次数: 730)

2013-4-28 23:34 上传

点击文件名下载附件

mps777

3、中职类的，由期中、期末、平时成绩所求的总评成绩的分析
此楼内容很少，因为上面的都已经处理过了，各位读者可以参考上面的做法。
这里只要介绍做成合并单元格下的平时、期中、期末、总评成绩的方法。
三、数据源
期中成绩
平时成绩
期末成绩
三、总评成绩

代码：

1. 
   
2. select t1.* from
   
3. (select 姓名,'期中' as 分类,'机械' as 科目,机械 as 成绩 from [期中$a2:d] union all
   
4. select 姓名,'期中','设计',设计 from [期中$a2:d] union all
   
5. select 姓名,'期中','力学',力学 from [期中$a2:d] union all
   
6. select 姓名,'期末','机械',机械 from [期末$a2:d] union all
   
7. select 姓名,'期末','设计',设计 from [期末$a2:d] union all
   
8. select 姓名,'期末','力学',力学 from [期末$a2:d] union all
   
9. select 姓名,'平时','机械',机械 from [平时$a2:d] union all
   
10. select 姓名,'平时','设计',设计 from [平时$a2:d] union all
    
11. select 姓名,'平时','力学',力学 from [平时$a2:d])t1, [期末$a2:d]t2 where t1.姓名=t2.姓名

复制代码

生成数据透视表后，基于有转入退学等情况，所以总评计算方法，有些是取平时+期中+期末，有些是平时+期末，当然各成绩所点比例不一样，所以在插入总评计算字段时，请输入如下公式：（提示：在此我取平时占50%，期中占20%，期末占30%；如果期中后转入的学生；平时占50%，期末占50%）

1. =IF(期中=0,平时/2+期末/2,平时/2+期中*0.2+期末*0.3)
   

复制代码

三、各科补考名单

代码：
1、按科目排序代码：

1. 
   
2. select 姓名,科目,成绩 from
   
3. (select 姓名,总评 as 成绩,'机械' as 科目 from [总评$a6:M] union all
   
4. select 姓名,总评1,'力学'  from [总评$a6:M] union all
   
5. select 姓名,总评2,'设计' from [总评$a6:M]) where 成绩<60

复制代码

2、按姓名排序代码：

1. 
   
2. select 姓名,科目,成绩 from
   
3. (select 姓名,总评 as 成绩,'机械' as 科目 from [总评$a6:M] union all
   
4. select 姓名,总评1,'力学'  from [总评$a6:M] union all
   
5. select 姓名,总评2,'设计' from [总评$a6:M]) where 成绩<60 order by 1
   

复制代码

我的数据表与代码
期中、期末数据.zip (39.2 KB, 下载次数: 533)

2013-4-28 23:59 上传

点击文件名下载附件

                            期中、期末代码.zip (429 Bytes, 下载次数: 473)

2013-4-28 23:59 上传

点击文件名下载附件

mps777

4、同一个文件夹里，引用各班的成绩的分析（只是合并）

这个没什么好说的，就是合并，前提各班要一致，如果不一致（指科目顺序有先后），可以在SQL语句里列出科目名；
我这里的数据源演示是用D盘目录来做， 如果各读者有兴趣尝试的话，可以将D:\同一个文件夹里，多张班成绩合并\这里改掉，
提示：
初一1班.xlsx指工作簿名
Sheet1$指你的数据源名
如果数据源工作簿名为abc.xlsx,工作表名为成绩
那么书写是[……：\……\abc.xlsx].[成绩$]
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
我的代码

1. 
   
2. select * from [D:\同一个文件夹里，多张班成绩合并\初一1班.xlsx].[Sheet1$]
   
3. union all
   
4. select * from [D:\同一个文件夹里，多张班成绩合并\初一2班.xlsx].[Sheet1$]
   
5. union all
   
6. select * from [D:\同一个文件夹里，多张班成绩合并\初一3班.xlsx].[Sheet1$]

复制代码

我的数据表
同一个文件夹里，多张班成绩合并.zip (38.8 KB, 下载次数: 604)

2013-4-29 00:15 上传

点击文件名下载附件

mps777

5、动态选择班别、科目得到所要的成绩表
呵呵，这个有意思，请看看动画。
五、单科分数选择变化表

代码：

1. 
   
2. select t1.班别,t1.姓名,t1.科目,t1.成绩 from
   
3. (select '全级' as 级,班别,姓名,语文 as 成绩,'语文' as 科目 from [三班汇总$]
   
4. union all
   
5. select '全级',班别,姓名,数学,'数学' from [三班汇总$]
   
6. union all
   
7. select '全级',班别,姓名,英语,'英语' from [三班汇总$]
   
8. union all
   
9. select '全级',班别,姓名,政治,'政治' from [三班汇总$]
   
10. union all
    
11. select '全级',班别,姓名,总分,'总分' from [三班汇总$] )t1,[单科分数选择变化表$F1:I2]t2
    
12. where iif(t2.班别='全级',t1.级,t1.班别)=t2.班别 and t1.科目=t2.科目 and (t1.成绩 between t2.起始分数 and t2.截止分数)

复制代码

统计分数段人数代码：

1. 
   
2. select 班别,count(*) as 分数段人数 from [单科分数选择变化表$] where 班别 group by 班别
   

复制代码

五、动态科目班别生成表

代码：

1. transform sum(成绩) select 班别,姓名 from
   
2. (select t1.* from
   
3. (select '全级' as 级,班别,姓名,语文 as 成绩,'语文' as 科目 from [三班汇总$]
   
4. union all
   
5. select '全级',班别,姓名,数学,'数学' from [三班汇总$]
   
6. union all
   
7. select '全级',班别,姓名,英语,'英语' from [三班汇总$]
   
8. union all
   
9. select '全级',班别,姓名,政治,'政治' from [三班汇总$]
   
10. union all
    
11. select '全级',班别,姓名,总分,'总分' from [三班汇总$])t1 inner join [动态表$]t2 on t1.班别=t2.班别 and t1.科目=t2.科目) group by 级,班别,姓名 pivot 科目

复制代码

我的数据与代码
多班动态演示表数据.zip (41.09 KB, 下载次数: 779)

2013-4-29 00:18 上传

点击文件名下载附件

                        多班动态演示表代码.zip (594 Bytes, 下载次数: 618)

2013-4-29 00:18 上传

点击文件名下载附件

lzqmsy

等待学习,先谢了!!!

xhcyi

mps777 发表于 2013-4-28 22:58
占楼，待编辑

样本啊老师

一只狗三只眼

小麦给大家分享和学习与交流！

一只狗三只眼

mps777 发表于 2013-4-28 22:58
5、动态选择班别、科目得到所要的成绩表
呵呵，这个有意思，请看看动画。
五、单科分数选择变化表

看得我晕头转向，但我还是要力挺麦老师一把，我太厉害了，你是我的偶象，钦佩至极！