时间戳

首页 > 解决方案 > SQL Join 重复 (M:N)

sql - SQL Join 重复 (M:N)

问题描述

如何在 SQL 中将一个表与另一个表多次连接？说明性示例：

表“情侣”：

+------+------+------+
|  ID  | ID_1 | ID_2 |
+------+------+------+
|   1  |  123 |  456 |
+------+------+------+

表“信息”：

+-----+-----------+-----------+--------+--------+
| ID  | FirstName | LastName  | Gender | Season |
+-----+-----------+-----------+--------+--------+
| 123 | Jon       | Snow      | Male   |      6 |
| 456 | Daenerys  | Targaryen | Female |      6 |
| 123 | Jon       | Targaryen | Male   |      7 |
+-----+-----------+-----------+--------+--------+

现在我需要一个组合结果，它需要是“最新的”（Info.Season必须是最高的，但您不能删除过时的行）：

期望的结果：

+-------------+------------+----------+-------------+------------+----------+
| FirstName_1 | LastName_1 | Gender_1 | FirstName_2 | LastName_2 | Gender_2 |
+-------------+------------+----------+-------------+------------+----------+
| Jon         | Targaryen  | Male     | Daenerys    | Targaryen  | Female   |
+-------------+------------+----------+-------------+------------+----------+

我不知道如何解决这个问题，ID 不是唯一的，我需要“多次”加入Info表。

标签： sqljoin

解决方案

您可以使用 SQL 窗口函数对每个玩家进行排序，从而选择玩家的最新状态season。然后，您需要将信息表加入到couples夫妻中每个人的表中。

actor_latestCTE

ID  FIRSTNAME   LASTNAME    GENDER  SEASON  LAST_CHANGE
123 Jon         Targaryen   Male    7       1
123 Jon         Snow        Male    6       2
456 Daenerys    Targaryen   Female  6       1

生成的 SQL

with actor_latest (id, firstname,lastname,gender,season, last_change) as (
    select
        id
      , firstname
      , lastname
      , gender
      , season
      , rank() over (partition by id order by season desc) as last_change
    from info
)
select
    left_partner.firstname as firstname_1
  , left_partner.lastname as lastname_1
  , left_partner.gender as gender_1
  , left_partner.season as season_1
  , right_partner.firstname as firstname_2
  , right_partner.lastname as lastname_2
  , right_partner.gender as gender_2
  , right_partner.season as season_2
from
    couples c
    join actor_latest left_partner on c.id_1 = left_partner.id and left_partner.last_change=1
    join actor_latest right_partner on c.id_2 = right_partner.id and left_partner.last_change=1

结果

FIRSTNAME_1 LASTNAME_1 GENDER_1 SEASON_1 FIRSTNAME_2    LASTNAME_2  GENDER_2 SEASON_2
Jon         Targaryen  Male     7        Daenerys       Targaryen   Female   6

SQL小提琴

推荐阅读