首页 新闻 搜索 专区 学院

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

0
悬赏园豆:100 [待解决问题]

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

 

 

 

苍烟落照的主页 苍烟落照 | 初学一级 | 园豆:104
提问于:2018-03-24 16:02
< >
分享
所有回答(2)
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

清海扬波 | 园豆:852 (小虾三级) | 2018-03-24 17:31
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

你的是正确答案,顶!d=====( ̄▽ ̄*)b

支持(1) 反对(0) Shendu.CC | 园豆:1914 (小虾三级) | 2018-03-26 10:00
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册