用SQl来做学生成绩分析

一只狗三只眼

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

能不能来个简单一点的，共大家学习！

七夕、

首页已经没了,,哈哈
小麦,bingo!!

moon_2000

楼主下真功夫了。不光看，而且动手练。确实应该这样学习。里面的语句有的还有些不懂。不过这种学习方法和为论坛做贡献的精神必须顶！

mps777

6、非子查询的各科排名
昨天香帅版主说过，各科排名可以用join来替代子查询得到排名；小麦想了一天，终于想出来了，生成速度很快，期待版主、各位老师指正，谢谢！
经香帅版主的指点，原代码有误，现已更正了，好像越写越长……{:soso_e109:}
数据源

六、不取用子查询的各科排名

代码：
用的是多表查询+内部查询（left join )来完成
如下是正确代码，解决出现多个最高分不是第1名的问题

1. select t3.*,li.力学名次 from
   
2. (select t2.*,se.设计名次 from
   
3. (select t1.*,ji.机械名次 from [期中$]t1
   
4. left join
   
5. (select a.机械,count(*) as 机械名次 from
   
6. (select distinct 机械 from [期中$])a,(select distinct 机械 from [期中$])b
   
7. where a.机械<=b.机械 group by a.机械)ji on t1.机械=ji.机械)t2
   
8. left join
   
9. (select a.设计,count(*) as 设计名次 from
   
10. (select distinct 设计 from [期中$])a,(select distinct 设计 from [期中$])b
    
11. where a.设计<=b.设计 group by a.设计)se on t2.设计=se.设计)t3
    
12. left join
    
13. (select a.力学,count(*) as 力学名次 from
    
14. (select distinct 力学 from [期中$])a,(select distinct 力学 from [期中$])b
    
15. where a.力学<=b.力学 group by a.力学)li on t3.力学=li.力学

复制代码

如下是错误代码，没有解决出现最高分为第1名的问题

1. select t5.*,t6.设计名次 from
   
2. (select t3.*,t4.力学名次 from
   
3. (select t1.*,t2.机械名次
   
4. from [期中$]t1 left join
   
5. (select a.姓名,count(*) as 机械名次 from [期中$]a,[期中$]b
   
6. where a.机械<=b.机械 group by a.姓名)t2
   
7. on t1.姓名=t2.姓名)t3 left join
   
8. (select
   
9. a.姓名,count(*) as 力学名次 from [期中$]a,[期中$]b where a.力学<=b.力学 group by a.姓名)t4
   
10. on t3.姓名=t4.姓名)t5 left join
    
11. (select a.姓名,count(*) as 设计名次 from
    
12. [期中$]a,[期中$]b where a.设计<=b.设计 group by a.姓名)t6
    
13. on t5.姓名=t6.姓名

复制代码

六、以学生为序，各科排名
这个是在如何用join来替代子查询的情况下，无意想到的一种结果，各位读者可以看看，不知成绩排名里有没有这种情况的。

代码：

1. transform sum(设计名次)
   
2. select 姓名,科目,成绩 from
   
3. (select t1.姓名,'设计' as 科目,t1.设计 as 成绩,'设计名次' as 分类,ji.设计名次 from [期中$]t1
   
4. left join
   
5. (select a.设计,count(*) as 设计名次 from
   
6. (select distinct 设计 from [期中$])a,(select distinct 设计 from [期中$])b
   
7. where a.设计<=b.设计 group by a.设计)ji on t1.设计=ji.设计
   
8. union all
   
9. select t1.姓名,'力学',t1.力学,'力学名次' as 分类,ji.力学名次 from [期中$]t1
   
10. left join
    
11. (select a.力学,count(*) as 力学名次 from
    
12. (select distinct 力学 from [期中$])a,(select distinct 力学 from [期中$])b
    
13. where a.力学<=b.力学 group by a.力学)ji on t1.力学=ji.力学
    
14. union all
    
15. select t1.姓名,'机械',t1.机械,'机械名次' as 分类,ji.机械名次 from [期中$]t1
    
16. left join
    
17. (select a.机械,count(*) as 机械名次 from
    
18. (select distinct 机械 from [期中$])a,(select distinct 机械 from [期中$])b
    
19. where a.机械<=b.机械 group by a.机械)ji on t1.机械=ji.机械) group by 姓名,科目,成绩 pivot 分类

复制代码

我的数据与代码
非子查询的各科排序数据.zip (25.64 KB, 下载次数: 313)

2013-4-30 15:24 上传

点击文件名下载附件

                     非子查询的各科排序代码.zip (629 Bytes, 下载次数: 261)

2013-4-30 15:24 上传

点击文件名下载附件

谢谢大家的观看与建议，以后遇到学生成绩的一些效果且可以用SQL来完成的，小麦都会一一补充。

gxt1988

mps777 发表于 2013-4-30 09:35
6、非子查询的各科排名
昨天香帅版主说过，各科排名可以用join来替代子查询得到排名；小麦想了一天，终于想 ...

1. select t5.*,t6.设计名次 from   (select t3.*,t4.力学名次 from   (select t1.*,t2.机械名次 from [期中$]t1 ,   (select a.姓名,count(*) as 机械名次 from [期中$]a,[期中$]b where a.机械<=b.机械 group by a.姓名)t2   where t1.姓名=t2.姓名)t3 ,   (select a.姓名,count(*) as 力学名次 from [期中$]a,[期中$]b where a.力学<=b.力学 group by a.姓名)t4   where t3.姓名=t4.姓名)t5 ,   (select a.姓名,count(*) as 设计名次 from [期中$]a,[期中$]b where a.设计<=b.设计 group by a.姓名)t6   where t5.姓名=t6.姓名

复制代码

这个代码和你那个有什么区别  是速度上的区别？  我把代码中的 left join 全部换成,  on 全部换成 where

amiaosheng

小麦老师很是强大的 羡慕
那些图片很精致 请问是用什么工具弄的呀？

amazeyeli

可以看出使用不规范的数据源会使得数据的后续处理显得更为繁琐

附件规范数据源并只写了前面2道题的语句仅为参考

一个班.rar (52.63 KB, 下载次数: 161)

2013-4-30 22:57 上传

点击文件名下载附件

ps:建表时请参考数据库范式

mps777

amazeyeli 发表于 2013-4-30 22:58
可以看出使用不规范的数据源会使得数据的后续处理显得更为繁琐

附件规范数据源并只写了前面2道题的语句仅 ...

是啊，先合并再处理，这样就避免多写语句，而且写起来也容易。
谢谢老师的指点

在和弦处等你

将原来的成绩表简单合并后写的各科成绩排名表SQL

1. TRANSFORM LAST(T.排名)
   
2. SELECT T.姓名 FROM
   
3.              (SELECT B.姓名, B.科目&"排名" AS 科目, B.排名 FROM
   
4.                           (SELECT A.姓名, A.科目, A.成绩, SUM(1) AS 排名 FROM [Sheet1$] A INNER JOIN [Sheet1$] B ON A.科目=B.科目 And A.成绩<=B.成绩 GROUP BY A.姓名, A.科目, A.成绩) B UNION ALL
   
5.                SELECT B.姓名, B.科目&"成绩" AS 科目, B.成绩 FROM
   
6.                           (SELECT A.姓名, A.科目, A.成绩, SUM(1) AS 排名 FROM [Sheet1$] A INNER JOIN [Sheet1$] B ON A.科目=B.科目 And A.成绩<=B.成绩 GROUP BY A.姓名, A.科目, A.成绩) B) T
   
7. GROUP BY T.姓名
   
8. PIVOT T.科目

复制代码

每天都能从小麦老师，和香帅老师处学到新东西{:soso_e121:}

题外话：感觉第五题动态科目生成表中，'全级' as 级 可以不要。

Office

谢谢！！！