# 在面试时候遇到了一道不会的sql查询题，有没有人愿意指点一下？

0

You are given two tables, teams and matches, with the following structures:

create table teams ( team_id integer not null, team_name varchar(30) not null, unique(team_id) ); create table matches ( match_id integer not null, host_team integer not null, guest_team integer not null, host_goals integer not null, guest_goals integer not null, unique(match_id) );

Each record in the table teams represents a single soccer team. Each record in the table matchesrepresents a finished match between two teams. Teams (host_team, guest_team) are represented by their IDs in

the teams table (team_id). No team plays a match against itself. You know the result of each match (that is, the number of goals scored by each team).

You would like to compute the total number of points each team has scored after all the matches described in the table. The scoring rules are as follows:

• If a team wins a match (scores strictly more goals than the other team), it receives three points.

• If a team draws a match (scores exactly the same number of goals as the opponent), it receives one point.

• If a team loses a match (scores fewer goals than the opponent), it receives no points.

•如果一支球队赢得一场比赛（比另一支球队进球多得多），它会得到三分。

•如果一支球队进行了一场比赛（得分与对手完全相同），它会得到一分。

•如果一支球队输了一场比赛（比对手少得分），那么它不会得分。

Write an SQL query that returns a ranking of all teams (team_id) described in the table teams. For each team you should provide its name and the number of points it received after all described matches (num_points). The table should be ordered by num_points (in decreasing order). In case of a tie, order the rows by team_id (in increasing order). For example, for

0

select a.*,b.num_points
from teams a
left join (
select team_id,sum(num_points) as num_points
from
(
select host_team as team_id,host_goals as num_points
from matches
union all
select gust_team,guest_goals
from matches
)a
group by team_id
)b on a.team_id=b.team_id

1

select teams.*,ISNULL(points.num_points,0) as num_points from teams left join
(select host_team,SUM(hostpoint) as num_points from
(select host_team,(case when host_goals>guest_goals then 3 when host_goals=guest_goals then 1 else 0 end) as hostpoint
from matches
union
select guest_team,(case when host_goals>guest_goals then 0 when host_goals=guest_goals then 1 else 3 end) as guestpoint
from matches) point group by host_team) points on teams.team_id=points.host_team
order by points.num_points desc,teams.team_id asc

ndgail | 园豆：148 (初学一级) | 2018-03-24 18:08

您需要登录以后才能回答，未注册用户请先注册