Oracle 学习笔记之二 足球赛程统计问题

  下面是一道 SQL 常见问题，觉得比较典型就拿出来。

  题目描述：

  球队表:

球队代码	球队名称
001	巴萨
002	皇马
003	巴伦西亚
004	国足

  球队比赛成绩表：

主场球队	客场球队	主场进球	客场进球
001	002	3	1
003	001	2	2
002	003	0	1

  要求通过 sql 获取下面结果：

  得分计算：球队获胜一场得3分，平一场得1分，其他情况不得分。

  要求把所有球队的比赛相关成绩按上图示例的 sql 取数结果显示，并按球队编号升序排序。

  相关脚本：

-- 创建表格
create table footballTeam(
  team_id varchar2(8), --球队编号
  team_name varchar2(32) --–球队名称
);
create table footballMatch(
  hostteam_id varchar2(8), --主场球队编号
  guestteam_id varchar2(8), --客场球队编号
  host_goal number,--主场球队进球数
  guest_goal number--客场球队进球数
);
-- 插入数据
insert into footballteam values('001','巴萨');
insert into footballteam values('002','皇马');
insert into footballteam values('003','巴伦西亚');
insert into footballteam values('004','国足');

insert into footballmatch values('001','002',3,1);
insert into footballmatch values('003','001',2,2);
insert into footballmatch values('002','003',0,1);
insert into footballmatch values('002','001',0,1);

commit;

  楼主觉得这道题只需以正常的思维去想，就简单了。觉得有一点可以取巧，就是 sql 结果中有些字段可以通过其他字段计算所得，如果想一次性全显示出来，似乎又不能计算虚拟字段，问题出来了。这里有个办法，可以将不需计算的字段先取出成一张表 t 再以这张表格的字段计算的相应的字段，例如：得分=获胜场次* 3+ 打平场次，这样就可以节省很多代码了。上代码：

SELECT TEAM_ID,
       TEAM_NAME,
       总比赛场次,
       获胜场次,
       打平场次,
       (总比赛场次 - 获胜场次 - 打平场次) AS 输球场次,
       进球数,
       失球数,
       获胜场次 * 3 + 打平场次 AS 得分
  FROM (SELECT A.TEAM_ID,
               A.TEAM_NAME,
               (SELECT COUNT(*)
                  FROM FOOTBALLMATCH
                 WHERE HOSTTEAM_ID = A.TEAM_ID
                    OR GUESTTEAM_ID = A.TEAM_ID) AS 总比赛场次,
               (SELECT COUNT(*)
                  FROM FOOTBALLMATCH
                 WHERE (HOSTTEAM_ID = A.TEAM_ID AND HOST_GOAL > GUEST_GOAL)
                    OR (GUESTTEAM_ID = A.TEAM_ID AND HOST_GOAL < GUEST_GOAL)) AS 获胜场次,
               (SELECT COUNT(*)
                  FROM FOOTBALLMATCH
                 WHERE (HOSTTEAM_ID = A.TEAM_ID AND HOST_GOAL = GUEST_GOAL)
                    OR (GUESTTEAM_ID = A.TEAM_ID AND HOST_GOAL = GUEST_GOAL)) AS 打平场次,
               (SELECT SUM(HOST_GOAL)
                  FROM FOOTBALLMATCH
                 WHERE HOSTTEAM_ID = A.TEAM_ID) +
               (SELECT SUM(GUEST_GOAL)
                  FROM FOOTBALLMATCH
                 WHERE GUESTTEAM_ID = A.TEAM_ID) AS 进球数,
               (SELECT SUM(GUEST_GOAL)
                  FROM FOOTBALLMATCH
                 WHERE HOSTTEAM_ID = A.TEAM_ID) +
               (SELECT SUM(HOST_GOAL)
                  FROM FOOTBALLMATCH
                 WHERE GUESTTEAM_ID = A.TEAM_ID) AS 失球数
          FROM FOOTBALLTEAM A) T
 ORDER BY T.TEAM_ID;

  

  对于类似的题目都可用此法，希望对你有帮助。欢迎提供更好的方法！