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

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) );

您会得到两张表格，球队和比赛，其结构如下：

创建表组（team_id integer not null，team_name varchar（30）not null，unique（team_id））; 创建表匹配（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:

表队中的每个记录代表一个足球队。 表中的每个记录匹配表示两个团队之间完成的匹配。 团队（host_team，guest_team）由其ID代表

队表（team_id）。 没有球队对自己进行比赛。 你知道每场比赛的结果（也就是每个球队得分的进球数）。

您希望计算表中描述的所有匹配项后每个团队得分的总点数。 得分规则如下：

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.

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

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

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

编写一个SQL查询，返回表队中描述的所有团队的排名（team_id）。 对于每个团队，您应提供其名称以及所有描述匹配（num_points）后收到的点数。 该表应该按num_points排序（按降序排列）。 如果是平1局，请按team_id排序（按递增顺序）。 例如，for

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