为ipl数据库编写SQL查询

Question

在每一场比赛中，找出球场内每场比赛的平均得分(两队的总得分)。您可以从ball_by_ball表中获得分数。输出，按每场比赛平均运行的降序排列。

表：

venue - venue_id,
        venue_name

match - match_id,
        team1,
        team2,
        venue_id

ball_by_ball - match_id,
               over_id,
               ball_id,
               runs_scored

这是我到目前为止编写的代码：

select venue_name,
    (select sum(runs_scored)
     from venue
     inner join match on venue.venue_id = match.venue_id
     inner join ball_by_ball on match.match_id = ball_by_ball.match_id
     group by venue_name)
     /
     (select count(match_id)
      from venue
      inner join match on venue.venue_id = match.venue_id
      group by venue_name) as avg_runs 
from venue

我搞错了

错误:由用作表达式的子查询返回的多行

我对SQL很陌生--请帮帮我。

Answer 1

您可以首先找到每场比赛的成绩，然后按以下方式使用average：

Select venue_id, avg(run_per_match) as avg_run_per_venue from
(select match.match_id, venue.venue_id
       sum(runs_scored) run_per_match
     from venue
     inner join match on venue.venue_id = match.venue_id
     inner join ball_by_ball on match.match_id = ball_by_ball.match_id
     group by match.match_id, venue.venue_id) t
Group by venue_id