sql - 使用SQL查询在Football数据库中查找卷土重来

我有四张桌子。
比赛

 | id    | HomeTeamID  | AwayTeamID |
 --------|-------------|------------
 | 1     | 1           | 2
 | 2     | 1           | 3
 | 3     | 3           | 1

目标

 | id    | MatchID     | Minute    | TeamID
 --------|-------------|---------- |---------
 | 1     | 1           |     3     |   2
 | 2     | 1           |     5     |   1
 | 3     | 1           |     15    |   1
 | 4     | 2           |     43    |   3
 | 5     | 2           |     75    |   1
 | 6     | 2           |     85    |   1
 | 7     | 3           |     11    |   1
 | 8     | 3           |     13    |   3
 | 9     | 3           |     77    |   3

团队：

 | id    | Name        |
 --------|-------------|
 | 1     | Chelsea     |
 | 2     | Arsenal     |
 | 3     | Tottenham   |

管理者：

 | id    | Name        | TeamID     |
 --------|-------------|-------------
 | 1     | Conte       |    1
 | 2     | Wenger      |    2
 | 3     | Pochettino  |    3

我想知道经理们复出的次数。例如，孔蒂的球队在第一场和第二场比赛中丢了第一个球，但他们赢了。所以孔蒂有两次复出。波切蒂诺在第三场比赛中有1次复出。我想用sql查询找到它。
我找到了每队比赛的第一个进球。但经过一些步骤，我正在失去我正在做的。

SELECT MatchID, MIN(minute), g.TeamID
FROM Goals g
JOIN Managers m ON m.TeamID = g.TeamID
GROUP BY MatchID, g.TeamID

最佳答案

在这里“复出”意味着球队丢了第一个球，但球队赢得了这场比赛。
我使用2个常规子查询，1）winners包含每个游戏中的MatchID和TeamID而不是以平局结束。（2）包括在比赛中打进第一个进球的那些人。
因此，使用以下方法连接这些子查询：
first_goals
给我们这些比赛，在那里球队赢了，但没有取得第一个进球（即“复出”）。
最后，我们使用带有TeamID和on winners.MatchID = first_goals.MatchID and winners.TeamID <> first_goals.TeamID表的简单联接：

with Goals(id    , MatchID    , Minute   ,TeamID)   as (
    select 1     , 1           ,     3     ,   2 union all
    select  2     , 1           ,     5     ,   1 union all
    select  3     , 1           ,     15    ,   1 union all
    select 4     , 2           ,     43    ,   3 union all
    select  5     , 2           ,    75    ,   1 union all
    select 6     , 2           ,     85    ,   1 union all
    select  7     , 3           ,     11    ,   1 union all
    select  8     , 3           ,     13    ,   3 union all
    select 9     , 3           ,     77    ,   3
),
Teams (id, Name) as(
    select 1     ,'Chelsea' union all
    select  2     ,'Arsenal' union all
    select  3     ,'Tottenham'
),
Managers(id, Name, TeamID) as (
select  1    ,'Conte',    1 union all
select  2     ,'Wenger',   2 union all
select  3     ,'Pochettino',    3
 )

select winners.TeamID, winners.MatchID, Teams.Name, Managers.Name from  (
    select t1.* from
    (
        select TeamID, MatchID, count(*) as goal_scored  from Goals
        group by TeamID, MatchID
    )t1
    inner join
    (
        select MatchID, max(goal_scored) as winner_goals_cnt from (
            select TeamID, MatchID, count(*) as goal_scored  from Goals
            group by TeamID, MatchID
        )t
        group by MatchID
        having min(goal_scored) <> max(goal_scored)
    )t2
    on t1.MatchID = t2.MatchID and t1.goal_scored = t2.winner_goals_cnt
) winners
inner join
(
    select * from (
    select Goals.*, row_number() over(partition by MatchID order by  Minute, id) rn from Goals
    ) f
    where rn = 1
) first_goals
on winners.MatchID = first_goals.MatchID and winners.TeamID <> first_goals.TeamID
inner join Teams
on winners.TeamID = Teams.id
inner join Managers
on winners.TeamID = Managers.TeamID