Oracle的一道查询题——找出在同一个操场进行比赛的各项目名称及其冠军的姓名

做Oracle作业时遇到一道比较复杂的题，写个博客记录一下 ^_^

题目描述

找出在同一个操场进行比赛的各项目名称及其冠军的姓名。

本题有用到的数据库：

1. 比赛项目（item）表
   

2. 成绩（grade）表
   

3. 运动员信息（sporter）表
   

解决思路

1. 寻找问题的最根本、最原始子问题
2. 将已查询到的表与其他已知表进行连接，进而得到接近题目要求的格式
3. 如此反复，最终达到题目的要求

解决方法

1. 首先将问题简化，要查询项目名称、冠军姓名，首先查询其对应的id
2. 项目号itemid和对应最高分（冠军）max_mark在成绩表中可以直接通过组函数查询

select itemid, max(mark)max_mark from grade
group by itemid

查询结果：

3. 根据得到的每个项目itemid的最高分max_mark查询对应的运动员号sporterid

这里需要连接第二步得到的表（标记为表a）和成绩表grade（记为表g）
g.itemid = a.itemid

select g.sporterid spid,g.itemid from grade g,(
select itemid, max(mark)max_mark from grade
group by itemid) a
where g.itemid = a.itemid --表连接
and g.mark = a.max_mark   --查询条件

查询结果：

4. 得到上面的表之后，通过连接上表（记为表table1）和运动员信息表sporter（记为表s，获得对应冠军姓名）以及项目信息表item（记为表i，得到项目名称和对应所在操场）

连接方法：
table1.itemid = i.itemid
table1.spid = s.sporterid

select i.location 操场, i.itemname 项目名称, s.name 冠军姓名 from sporter s, item i,(

select g.sporterid spid,g.itemid from grade g,(
select itemid, max(mark)max_mark from grade
group by itemid) a
where g.itemid = a.itemid
and g.mark = a.max_mark) table1

where table1.itemid = i.itemid
and table1.spid = s.sporterid

5. 最后再加上按操场名location排序，得到最终的答案

select i.location 操场, i.itemname 项目名称, s.name 冠军姓名 from sporter s, item i,(

select g.sporterid spid,g.itemid from grade g,(
select itemid, max(mark)max_mark from grade
group by itemid) a
where g.itemid=a.itemid
and g.mark=a.max_mark) table1

where table1.itemid=i.itemid
and table1.spid=s.sporterid
order by 1 desc;

查询结果：