mysql LEFT JOIN 2个带条件的表

f2uvfpb9 于 2023-11-16 发布在 Mysql

关注(0)|答案(1)|浏览(134)

我有两个表如下
第一个表，Segment_info
| 预订ID|网段id|乘客ID| SSRCode| SSR创建UTC|
| --|--|--|--|--|
| 123 | 371 | 251 |WCI| 2023-04-07 00：53：00.407000 UTC|
| 123 | 371 | 251 |MCI| 2023-04-07 03：20：07.547000 UTC|
| 123 | 371 | 251 |PB20| 2023-04-07 00：52：19.107000 UTC|
| 123 | 371 | 251 |AB25| 2023-04-08 06：58：54.950000 UTC|
| 124 | 372 | 261 |WCI| 2023-04-07 14：53：00.407000 UTC|
| 124 | 372 | 261 |MCI| 2023-04-07 12：20：07.547000 UTC|
第二桌，行李_信息
| 网段id|行李ID|重量|
| --|--|--|
| 371 | 1350 | 20 |
| 371 | 1351 | 28 |
| 372 | 1230 | 8 |
| 372 | 1231 | 24 |
| 372 | 1232 | 15 |
我想根据以下规则将Baggage_InfoMap到Segment_info。
1.当存在等于“AB 20”、“AB 25”、“AB 30”或“AB 40”的SSRCode时，首先Map具有相同SegmentID的baggage_info
1.如果不存在等于“AB 20”、“AB 25”、“AB 30”或“AB 40”的SSRCode，但显示了“PB 20”、“PB 25”、“PB 30”或“PB 40”，则将baggage_infoMap到具有相同SegmentID的最早SSRCreatedUTC
1.如果不满足上述2个条件，并且存在等于“WCI”或“MCI”的SSRCode，则将baggage_infoMap到具有相同SegmentID的最早SSRCreatedUTC
预期结果：
| 预订ID|网段id|乘客ID| SSRCode| SSR创建UTC|行李ID|重量|
| --|--|--|--|--|--|--|
| 123 | 371 | 251 |WCI| 2023-04-07 00：53：00.407000 UTC| NULL| NULL|
| 123 | 371 | 251 |MCI| 2023-04-07 03：20：07.547000 UTC| NULL| NULL|
| 123 | 371 | 251 |PB20| 2023-04-07 00：52：19.107000 UTC| NULL| NULL|
| 123 | 371 | 251 |AB25| 2023-04-08 06：58：54.950000 UTC| 1350 | 20 |
| 123 | 371 | 251 |AB25| 2023-04-08 06：58：54.950000 UTC| 1351 | 28 |
| 124 | 372 | 261 |WCI| 2023-04-07 14：53：00.407000 UTC| NULL| NULL|
| 124 | 372 | 261 |MCI| 2023-04-07 12：20：07.547000 UTC| 1230 | 8 |
| 124 | 372 | 261 |MCI| 2023-04-07 12：20：07.547000 UTC| 1231 | 24 |
| 124 | 372 | 261 |MCI| 2023-04-07 12：20：07.547000 UTC| 1232 | 15 |
现在我正在使用以下SQL

WITH 

segment_info AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY SegmentID ORDER BY
      CASE WHEN SSRCode IN ('AB20', 'AB25', 'AB30', 'AB40') THEN 1
      WHEN SSRCode IN ('PB20', 'PB25', 'PB30', 'PB40') THEN 2  
      WHEN SSRCode IN ('WCI', 'MCI') THEN 3
      ELSE 4 END,
      SSRCreatedUTC ASC
    ) AS rn
  FROM
    `Segment_info`
),

final_output AS (SELECT

    SI.BookingID,
    SI.SegmentID,
    SI.PassengerID,

    SI.SSRCode,
    SI.SSRCreatedUTC,

    BI.BaggageID,
    BI.Weight
FROM
  segment_info SI
LEFT JOIN
  `Baggage_Info` BI ON SI.SegmentID = BI.SegmentID
WHERE
  SI.rn = 1)

SELECT * FROM final_output

字符串
然而，它并没有给给予我想要的结果，有人能帮忙吗？谢谢！

mysql

来源：https://stackoverflow.com/questions/77365464/left-join-2-tables-with-conditions

1条答案

按热度按时间

xdyibdwo1#

您使用WHERE Si.rn=1过滤最佳数据集。但是，您不想过滤，而是限制连接。因此，将WHERE替换为AND。
我添加了raw_input_line_number，因为您在示例中对所需的结果进行了排序，并将rn添加到输出中。

With Segment_info_raw as 
( Select BookingID,BookingID+248 SegmentID, BookingID*10-979 PassengerID,SSRCode, SSRCreatedUTC,
row_number() over () as raw_input_line_number,
from (  
   select 123 BookingID, timestamp "2023-04-07 00:53:00.407000 UTC" SSRCreatedUTC,"WCI" SSRCode
union all select 123, timestamp "2023-04-07 03:20:07.547000 UTC", "MCI"
union all select 123, timestamp "2023-04-07 00:52:19.107000 UTC", "PB20"
union all select 123, timestamp "2023-04-08 06:58:54.950000 UTC", "AB25"
union all select 124, timestamp "2023-04-07 14:53:00.407000 UTC", "WCI"
union all select 124, timestamp "2023-04-07 12:20:07.547000 UTC", "MCI"
)
),
Baggage_Info as 
(
Select  371 SegmentID,  1350    BaggageID,  20 Weight
union all Select    371 ,   1351    ,   28
union all Select    372 ,   1230    ,   8
union all Select    372 ,   1231    ,   24
union all Select    372 ,   1232    ,   15
),

segment_info AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY SegmentID ORDER BY
      CASE WHEN SSRCode IN ('AB20', 'AB25', 'AB30', 'AB40') THEN 1
      WHEN SSRCode IN ('PB20', 'PB25', 'PB30', 'PB40') THEN 2  
      WHEN SSRCode IN ('WCI', 'MCI') THEN 3
      ELSE 4 END,
      SSRCreatedUTC ASC
    ) AS rn
  FROM
    `Segment_info_raw`
),

final_output AS (SELECT

    SI.BookingID,
    SI.SegmentID,
    SI.PassengerID,

    SI.SSRCode,
    SI.SSRCreatedUTC,

    BI.BaggageID,
    BI.Weight,
    rn,
    raw_input_line_number

FROM
  segment_info SI
LEFT JOIN
  `Baggage_Info` BI ON SI.SegmentID = BI.SegmentID
# WHERE SI.rn = 1 # This would filter the result that only the best matching lines are shown
AND   SI.rn = 1  # This restricts the join to the best matching, but does not do a filter on the first table to display
)

SELECT * FROM final_output
order by raw_input_line_number

字符串

赞(0）回复(0）举报 2023-11-16

我来回答

mysql LEFT JOIN 2个带条件的表

1条答案

相关问题

热门标签

最新问答