SQL分组排序与窗口函数解决学生排名查询
需积分: 0 54 浏览量
更新于2024-07-15
收藏 2.99MB PPTX 举报
"该资源主要探讨了SQL中的分组排序问题,特别关注如何利用窗口函数(如RANK())来解决学生排名的情况。通过实例分析,讲解了如何使用OVER子句进行分组和排序,以及如何结合GROUP BY和窗口函数进行聚合计算。"
在SQL查询中,分组排序是数据分析和报表生成时常见的需求。在这个问题中,目标是检索每学期每门课的学生排名情况,输出包括学期、课程号、学号、总评成绩和排名。排序规则是按学期降序,课程号升序,课程相同则按排名从高到低。
首先,我们来看如何使用窗口函数RANK()来实现这个功能。RANK()函数可以为每个分区内的行提供一个唯一的排名,基于指定的排序顺序。在这个例子中,我们可以使用LEFT()和RIGHT()函数从"O.xq"字段提取学期信息,然后通过LEFT JOIN操作将课程表(假设为O)和学生表(假设为E)连接起来,以获取必要的数据。查询的基本结构如下:
```sql
SELECT
LEFT(O.xq, 9) AS 学年,
RIGHT(O.xq, 2) AS 学期,
O.kh AS 课程号,
E.xh AS 学号,
E.zpcj AS 总评成绩,
RANK() OVER (
PARTITION BY O.xq, O.kh
ORDER BY E.zpcj DESC
) AS 排名
FROM
O
LEFT JOIN
E
ON
O.kh = E.kh AND O.gh = E.gh
ORDER BY
LEFT(O.xq, 9) DESC,
RIGHT(O.xq, 2) DESC;
```
这里的OVER子句被用来定义分组和排序。PARTITION BY O.xq, O.kh 将数据根据学期和课程号进行分区,而ORDER BY E.zpcj DESC 则指定了在每个分区内部,学生按总评成绩从高到低进行排名。
此外,OVER子句还可以与其他聚合函数结合使用,如SUM(), AVG(), MIN(), MAX()等,以在每个分组内进行计算。例如,要计算每学期每门课的总成绩、平均成绩、最小成绩和最大成绩,可以这样写:
```sql
SELECT
DISTINCT xq, kh, gh,
SUM(zpcj) OVER (PARTITION BY xq, kh) AS '学期总成绩',
AVG(zpcj) OVER (PARTITION BY xq, kh) AS '平均总评成绩',
MIN(zpcj) OVER (PARTITION BY xq, kh) AS '最小总评成绩',
MAX(zpcj) OVER (PARTITION BY xq, kh) AS '最大总评成绩'
FROM
E;
```
这里,OVER子句在每个由学期(xq)和课程号(kh)定义的分区上应用了聚合函数,为每门课每个学期计算出相应的统计信息。
SQL中的窗口函数和OVER子句提供了强大的数据分析能力,可以用于处理复杂的分组和排序任务,比如学生的学期排名和成绩统计。熟练掌握这些功能对于进行复杂的数据查询和报表生成至关重要。
2012-11-10 上传
2012-05-31 上传
点击了解资源详情
点击了解资源详情
点击了解资源详情
点击了解资源详情
点击了解资源详情
2023-07-28 上传
2023-06-10 上传
![](https://profile-avatar.csdnimg.cn/7676798af1be4237bd7509278bf14e01_carrie_colourful.jpg!1)
请你喝java~
- 粉丝: 6
- 资源: 1
最新资源
- 多模态联合稀疏表示在视频目标跟踪中的应用
- Kubernetes资源管控与Gardener开源软件实践解析
- MPI集群监控与负载平衡策略
- 自动化PHP安全漏洞检测:静态代码分析与数据流方法
- 青苔数据CEO程永:技术生态与阿里云开放创新
- 制造业转型: HyperX引领企业上云策略
- 赵维五分享:航空工业电子采购上云实战与运维策略
- 单片机控制的LED点阵显示屏设计及其实现
- 驻云科技李俊涛:AI驱动的云上服务新趋势与挑战
- 6LoWPAN物联网边界路由器:设计与实现
- 猩便利工程师仲小玉:Terraform云资源管理最佳实践与团队协作
- 类差分度改进的互信息特征选择提升文本分类性能
- VERITAS与阿里云合作的混合云转型与数据保护方案
- 云制造中的生产线仿真模型设计与虚拟化研究
- 汪洋在PostgresChina2018分享:高可用 PostgreSQL 工具与架构设计
- 2018 PostgresChina大会:阿里云时空引擎Ganos在PostgreSQL中的创新应用与多模型存储