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-5-1 13:32 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖已被收录到知识树中,索引项:SQL应用
分享和学习与交流!

TA的精华主题

TA的得分主题

发表于 2013-5-1 13:36 | 显示全部楼层
谢谢分享,收藏学习

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:00 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
虽然不会,但是还是来支持一下

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:02 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
做的不错,慢慢看看。

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:03 | 显示全部楼层
我们才是真正的劳动人民啊,劳动节都还在开工

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:06 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
不错,下来慢慢学习。

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:15 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
学习学习,真棒

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:21 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-5-1 14:42 | 显示全部楼层
用透视表来完成,方法独特,谢谢分享。

TA的精华主题

TA的得分主题

发表于 2013-5-1 15:05 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
mps777 发表于 2013-4-30 09:35
6、非子查询的各科排名
昨天香帅版主说过,各科排名可以用join来替代子查询得到排名;小麦想了一天,终于想 ...

各科排名:
  1. SELECT A.姓名,A.学科,A.成绩,count(B.成绩)+1 AS 排名 FROM
  2. (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
  3. UNION ALL
  4. SELECT 姓名,设计,'设计' FROM [期中$]
  5. UNION ALL
  6. SELECT 姓名,力学,'力学' FROM [期中$])A
  7. LEFT JOIN
  8. (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
  9. UNION ALL
  10. SELECT 姓名,设计,'设计' FROM [期中$]
  11. UNION ALL
  12. SELECT 姓名,力学,'力学' FROM [期中$])B
  13. ON A.学科=B.学科 AND A.成绩<B.成绩
  14. GROUP BY A.姓名,A.学科,A.成绩
复制代码
如需转置,可以这样:
  1. TRANSFORM MIN(排名)
  2. SELECT 姓名,学科,成绩 FROM
  3. (SELECT A.姓名,A.学科,A.成绩,count(B.成绩)+1 AS 排名 FROM
  4. (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
  5. UNION ALL
  6. SELECT 姓名,设计,'设计' FROM [期中$]
  7. UNION ALL
  8. SELECT 姓名,力学,'力学' FROM [期中$])A
  9. LEFT JOIN
  10. (SELECT 姓名,机械 AS 成绩,'机械' AS 学科 FROM [期中$]
  11. UNION ALL
  12. SELECT 姓名,设计,'设计' FROM [期中$]
  13. UNION ALL
  14. SELECT 姓名,力学,'力学' FROM [期中$])B
  15. ON A.学科=B.学科 AND A.成绩<B.成绩
  16. GROUP BY A.姓名,A.学科,A.成绩)
  17. GROUP BY 姓名,学科,成绩
  18. PIVOT 学科
复制代码

LEFT JOIN返回的是左表所有记录,和右表符合关联条件的记录。于是,在学科相同的条件下,根据A.成绩<B.成绩条件对成绩进行比较,那么,A表的最大值不可能在B表中存在比他大的值(因为两表的数据源是相同的),故其对应B表的记录为NULL,然后,使用COUNT(字段)进行统计,得出是0,然后用0+1,得出最大值的排名为1,也就是从1开始排名了。
如果A.成绩<B.成绩改为A.成绩<=B.成绩,那么A表的最大值在B表中必然存在,若是最大值有重复(比如同一学科存在两个100分或者更多),那么,使用COUNT进行计数时,其值必然>1,也就是说在最大值出现重复时,排名不是从1开始。

也可以这样:
  1. SELECT 姓名,学科,成绩,SUM(机械) AS 机械排名,SUM(设计) AS 设计排名,SUM(力学) AS 力学排名 FROM
  2. (SELECT A.姓名,'机械' AS 学科,A.机械 AS 成绩,COUNT(B.机械)+1 AS 机械,0 AS 设计,0 AS 力学 FROM [期中$]A LEFT JOIN [期中$]B ON A.机械<B.机械 GROUP BY A.姓名,A.机械
  3. UNION ALL
  4. SELECT A.姓名,'设计',A.设计,0,COUNT(B.设计)+1,0 FROM [期中$]A LEFT JOIN [期中$]B ON A.设计<B.设计 GROUP BY A.姓名,A.设计
  5. UNION ALL
  6. SELECT A.姓名,'力学',A.力学,0,0,COUNT(B.力学)+1 FROM [期中$]A LEFT JOIN [期中$]B ON A.力学<B.力学 GROUP BY A.姓名,A.力学)
  7. GROUP BY 姓名,学科,成绩
复制代码


评分

3

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-28 18:07 , Processed in 0.050374 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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