ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: mps777

[原创] 用SQl来做学生成绩分析

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-4-29 06:25 | 显示全部楼层
本帖已被收录到知识树中,索引项:SQL应用
mps777 发表于 2013-4-28 22:58
5、动态选择班别、科目得到所要的成绩表
呵呵,这个有意思,请看看动画。
五、单科分数选择变化表

能不能来个简单一点的,共大家学习!

TA的精华主题

TA的得分主题

发表于 2013-4-29 20:35 | 显示全部楼层
首页已经没了,,哈哈
小麦,bingo!!

TA的精华主题

TA的得分主题

发表于 2013-4-30 00:00 | 显示全部楼层
楼主下真功夫了。不光看,而且动手练。确实应该这样学习。里面的语句有的还有些不懂。不过这种学习方法和为论坛做贡献的精神必须顶!

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-30 09:35 | 显示全部楼层
本帖最后由 mps777 于 2013-4-30 15:24 编辑

6、非子查询的各科排名
昨天香帅版主说过,各科排名可以用join来替代子查询得到排名;小麦想了一天,终于想出来了,生成速度很快,期待版主、各位老师指正,谢谢!
经香帅版主的指点,原代码有误,现已更正了,好像越写越长……{:soso_e109:}
数据源
六、数据源.gif
六、不取用子查询的各科排名
不取用子查询的各科排名.gif
代码:
用的是多表查询+内部查询(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来替代子查询的情况下,无意想到的一种结果,各位读者可以看看,不知成绩排名里有没有这种情况的。
六、以学生为序,各科排名.gif
代码:

  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, 下载次数: 312)                      非子查询的各科排序代码.zip (629 Bytes, 下载次数: 260)

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

点评

代码不错,还可以再简化一下,另外,当最高分出现多个时,使用多表查询出来的排名就不是从1开始了,而这个问题可以用LEFT JOIN解决。加油!!!  发表于 2013-4-30 14:10

TA的精华主题

TA的得分主题

发表于 2013-4-30 11:29 | 显示全部楼层
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

点评

你这个就是多表查询,跟子查询不一样的。  发表于 2013-4-30 12:41

TA的精华主题

TA的得分主题

发表于 2013-4-30 11:59 | 显示全部楼层
小麦老师很是强大的 羡慕
那些图片很精致 请问是用什么工具弄的呀?

点评

用一个名为isee软件……  发表于 2013-4-30 12:42

TA的精华主题

TA的得分主题

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

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

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-30 23:24 | 显示全部楼层
amazeyeli 发表于 2013-4-30 22:58
可以看出使用不规范的数据源会使得数据的后续处理显得更为繁琐

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

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

TA的精华主题

TA的得分主题

发表于 2013-5-1 08:13 | 显示全部楼层
将原来的成绩表简单合并后写的各科成绩排名表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.科目
复制代码
各科成绩排名.png

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

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

TA的精华主题

TA的得分主题

发表于 2013-5-1 08:25 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 06:39 , Processed in 0.057035 second(s), 14 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表