sql - 如何在 Oracle SQL 19 中根据组填充所有行的空值？

Question

我在 Oracle SQL 19 中有下表：

+--------------+--------------+-------------------+------------------+-------------------+
|     Team     | Team Ranking | Team Ranking Date | Player Join Date |      Player       |
+--------------+--------------+-------------------+------------------+-------------------+
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Lionel Messi      |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Luis Suarez       |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Antoine Griezmann |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Ousmane Dembele   |
| FC Barcelona |            3 | 16-Jul-20         | (null)           | (null)            |
| FC Barcelona |            5 | 17-Jul-20         | (null)           | (null)            |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Eden Hazard       |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Sergio Ramos      |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Karim Benzema     |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Gareth Bale       |
| Real Madrid  |            2 | 15-Jul-20         | 16-Jul-20        | James Rodriguez   |
| Real Madrid  |            5 | 25-Jul-20         | (null)           | (null)            |
| Real Madrid  |            2 | 28-Jul-20         | (null)           | (null)            |
+--------------+--------------+-------------------+------------------+-------------------+

我想用球队的所有球员信息填写上表中的空值。换句话说，我希望结果如下所示：

+--------------+--------------+-------------------+------------------+-------------------+
|     Team     | Team Ranking | Team Ranking Date | Player Join Date |      Player       |
+--------------+--------------+-------------------+------------------+-------------------+
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Lionel Messi      |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Luis Suarez       |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Antoine Griezmann |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Ousmane Dembele   |
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Lionel Messi*     |
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Luis Suarez*      |
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Antoine Griezmann*|
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Ousmane Dembele*  |
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Lionel Messi*     |
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Luis Suarez*      |
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Antoine Griezmann*|
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Ousmane Dembele*  |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Eden Hazard       |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Sergio Ramos      |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Karim Benzema     |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Gareth Bale       |
| Real Madrid  |            2 | 15-Jul-20         | 16-Jul-20        | James Rodriguez   |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Eden Hazard*      |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Sergio Ramos*     |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Karim Benzema*    |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Gareth Bale*      |
| Real Madrid  |            5 | 25-Jul-20         | 16-Jul-20*       | James Rodriguez*  |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Eden Hazard*      |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Sergio Ramos*     |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Karim Benzema*    |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Gareth Bale*      |
| Real Madrid  |            2 | 28-Jul-20         | 16-Jul-20*       | James Rodriguez*  |
+--------------+--------------+-------------------+------------------+-------------------+

我在上表中为填写的值加了星标。

在这个例子中，我只展示了每个团队的两个排名变化。但是一个团队的排名可以无限次地改变。

有人可以告诉我如何在 Oracle SQL 19 中执行此操作吗？

任何帮助将不胜感激！

为了您的方便，下面是在 Oracle 中生成表的 sql 查询：

with tbl as (
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Lionel Messi' player from dual union
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Luis Suarez' player from dual union
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Antoine Griezmann' player from dual union
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Ousmane Dembele' player from dual union
    select 'FC Barcelona' team, 3 team_ranking, to_date('7/16/2020 04:18', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual union
    select 'FC Barcelona' team, 5 team_ranking, to_date('7/17/2020 06:54', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Eden Hazard' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Sergio Ramos' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Karim Benzema' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Gareth Bale' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/16/2020 14:44', 'MM/DD/YYYY HH24:MI') player_join_date,  'James Rodriguez' player from dual union
    select 'Real Madrid' team, 5 team_ranking, to_date('7/25/2020 10:02', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/28/2020 06:13', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual
)
select *
from tbl
order by team, team_ranking_date, player_join_date

标签： sqloracle

Answer 1

您需要分别考虑具有空玩家和非空玩家的集合。直接取非空玩家的集合。其余部分应用外部连接和交叉连接，并通过UNION ALL如下方式组合所有结果：

with  tbl2 as
  (    
    select * from tbl where player is not null
    union all
    select tt1.team, tt2.team_ranking, tt2.team_ranking_date, tt1.player_join_date, tt1.player
      from
      (
       (select t2.*
          from ( select * from tbl where player is null ) t1
         right join ( select * from tbl where player is not null ) t2 on t2.player = t1.player ) tt1
       cross join      
       (select t1.*
          from ( select * from tbl where player is null ) t1
         left join ( select * from tbl where player is not null ) t2 on t2.player = t1.player ) tt2
      )          
  )
 select * 
   from tbl2 
  order by team, team_ranking_date, player_join_date