问用Sql查询查找足球数据库中的回归

EN

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

with cte 
(
MatchID,TeamID,TotalGoalTime,NoOfGoals,ManagerName,comeback)
as(SELECT MatchID, g.TeamID,sum(minutea) as'TotalGoalTime' ,count(*)as'NoOfGoals',m.name as'ManagerName'
,comeback =ROW_NUMBER() OVER(PARTITION BY MatchID order by sum(minutea) desc) 
FROM Goals g
JOIN Managers m ON m.TeamID = g.TeamID
join [Teams] t on t.Id=g.TeamId
GROUP BY MatchID, g.TeamID,m.name )
Select MatchID,TeamID,NoOfGoals,ManagerName from cte where comeback =1

Team A  Team B
  0    -   1     //team b scores
  1    -   1     //team a scores
  2    -   1     //team a scores (comeback for a)
  2    -   2     //team b scores 
  2    -   3     //team b scores (comeback for b)
  3    -   3     //team a scores
  4    -   3     //team a scores (comeback for a)

DECLARE @matches TABLE
(   
    [id] TINYINT
   ,[HomeTeamID] TINYINT
   ,[AwayTeamID] TINYINT
);

DECLARE @Goals TABLE
(
    [id] TINYINT
   ,[MatchID] TINYINT
   ,[Minute] TINYINT
   ,[TeamID] TINYINT
);

DECLARE @Teams TABLE
(
    [id] TINYINT
   ,[Name] VARCHAR(12)
);

DECLARE @Managers TABLE
(
    [Id] TINYINT
   ,[Name] VARCHAR(12)
   ,[TeamID] TINYINT
);

INSERT INTO @matches ([id], [HomeTeamID], [AwayTeamID])
VALUES (1, 1, 2)
      ,(2, 1, 3)
      ,(3, 3, 1)
      ,(4, 1, 4);

INSERT INTO @Goals ([id], [MatchID], [Minute], [TeamID])
VALUES (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)
      ,(10, 4, 3, 1)
      ,(11, 4, 5, 4)
      ,(12, 4, 10, 4)
      ,(13, 4, 12, 1)
      ,(14, 4, 25, 1)
      ,(15, 4, 46, 4)
      ,(16, 4, 60, 4)
      ,(17, 4, 72, 4)
      ,(18, 4, 84, 4);

INSERT INTO @Teams ([id], [Name])
VALUES (1, 'Chelsea')
      ,(2, 'Arsenal')
      ,(3, 'Tottenham')
      ,(4, 'Real Madrid');

INSERT INTO @Managers ([Id], [Name], [TeamID])
VALUES (1, 'Conte', 1)
      ,(2, 'Wenger', 2)
      ,(3, 'Pochettino', 3)
      ,(4, 'Zidane', 4);

WITH DataSource AS
(
    SELECT m.[id]
          ,m.[HomeTeamID]
          ,m.[AwayTeamID]
          ,ROW_NUMBER() OVER (PARTITION BY m.[id] ORDER BY g.[minute]) AS [EventID]
          ,IIF
           (
                SUM(IIF(m.[HomeTeamID] = g.[teamID], 1, 0)) OVER (PARTITION BY m.[id] ORDER BY g.[minute] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 1
                =
                SUM(IIF(m.[AwayTeamID] = g.[teamID], 1, 0)) OVER (PARTITION BY m.[id] ORDER BY g.[minute] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                OR
                SUM(IIF(m.[HomeTeamID] = g.[teamID], 1, 0)) OVER (PARTITION BY m.[id] ORDER BY g.[minute] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
                =
                SUM(IIF(m.[AwayTeamID] = g.[teamID], 1, 0)) OVER (PARTITION BY m.[id] ORDER BY g.[minute] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -1
               ,IIF(m.[HomeTeamID] = g.[teamID], 'H', 'A') -- (H)ome come back, (A)way come ba
              ,'N' -- no come back
           ) AS [ComeBack]
    FROM @matches m
    INNER JOIN @Goals g
        ON m.[id] = g.[MatchID]
)
SELECT T.[Name]
FROM DataSource DS
INNER JOIN @Teams T
    ON IIF([ComeBack] = 'H', [HomeTeamID], [AwayTeamID]) = T.[id]
WHERE DS.[EventID] <> 1
    AND DS.[ComeBack] <> 'N';

Chelsea
Chelsea
Chelsea
Tottenham
Real Madrid
Real Madrid