如何编写MySQL查询获取同quoteid关联段的出发与到达信息

需要编写MySQL查询实现以下需求：

• 筛选quoteid=1的记录
• 对同一itenid，获取第一条数据的departure、at_dept字段
• 同时获取该itenid下第二条数据的arrival、at_arr字段
• 确保各字段与主键s.id（别名segmentIdPk）正确匹配

用户已尝试多种查询方式但未得到预期结果，尝试的查询如下：

尝试的查询1 #

Select s.id as segmentIdPk,  s.offer_id, 
s.itenid as d_itenid, s.departure, s.at_dept,
s.arrival, s.at_arr
from s_flight_offers_segments s 
where quoteid = 1

尝试的查询2 #

Select  d.segmentIdPk, ar.id,  d.d_itenid, ar.itenid, d.offer_id,
d.departure as outerdept, ar.arrival, ar.at_arr 
from
(
Select s.id as segmentIdPk,  s.offer_id, 
s.itenid as d_itenid, s.departure, s.at_dept,
s.arrival, s.at_arr
from s_flight_offers_segments s 
where quoteid = 1
  
order by s.id ASC 
)
as d  
Left Join s_flight_offers_segments ar on ar.itenid = d.d_itenid

尝试的查询3 #

Select  depart.*, sega.id as segPKA, sega.offer_id, sega.arrival as arrCity, sega.iataCode_arr, sega.at_arr, 
duration as inbound, sega.operating_code as carrierArrival, sega.stops as arr_stops
From 
(
Select    seg.id as segPK,seg.offer_id as oid,  seg.departure as departCity, seg.iataCode_dept, seg.at_dept,
seg.segmentId, seg.itenid , seg.stops,  duration as outbound,
seg.operating_code as carrierDeparture, seg.carrierCode
from 
s_flight_offers_segments seg   
where seg.quoteid  =   1
and
seg.id in (Select MIN(id) from s_flight_offers_segments group by itenid) 
) as depart
Left Join s_flight_offers_segments sega on sega.itenid = depart.itenid
where sega.quoteid  =  1
and
sega.id in (Select MAX(id) from s_flight_offers_segments group by itenid)

测试数据（用户提供） #

INSERT INTO Offer
  (id, name,   quoteid, resource)
VALUES
  (1, 'KP',   '1', 399),
  (2, 'MK',   '1', 244),
  (3, 'VM',   '1', 555),
  (4, 'KV',   '1', 300),
  (5, 'AJ',   '1', 200),
  (6, 'JG',   '1', 500),
  (7, 'TS',   '1', 600),
  (8, 'KB',   '1', 700)
;

INSERT INTO Itenary
  (id, offerid, quoteId)
VALUES
  (1,  '1',  '1'),
  (2,  '1',  '1'),
  (5,  '2',  '1'),
  (3,  '2',  '1'),
  (4,  '3',  '2'),
  (6,  '3',  '2'),
  (7,  '4',  '2'),
  (8,  '4',  '2')
;

INSERT INTO Segment
  (id, quoteId, offerId, departure, arrival, dept_time, arr_time)
VALUES
  (1,  '1',  '1', 'MEL', 'CMB', '2023-08-01 16:10:00', '2023-08-02 11:30:00' ),
  (2,  '1',  '1', 'CMB', 'BOM', '2023-08-02 14:35:00', '2023-08-02 20:10:00' ),
  (3,  '1',  '2', 'MEL', 'CMB', '2023-10-01 09:25:00', '2023-10-01 12:30:00' ),
  (4,  '1',  '2', 'CMB', 'BOM', '2023-10-01 23:40:00', '2023-10-02 02:15:00' ),
  
  (5,  '1',  '3', 'MEL', 'CMB', '2023-08-01 11:00:00', '2023-08-01 16:30:00' ),
  (6,  '1',  '3', 'CMB', 'BOM', '2023-08-02 09:20:00', '2023-08-02 11:05:00' ),
  (7,  '1',  '4', 'MEL', 'CMB', '2023-10-01 09:25:00', '2023-10-01 11:30:00' ),
  (8,  '1',  '4', 'CMB', 'BOM', '2023-10-01 16:40:00', '2023-10-02 07:15:00' )

使用窗口函数ROW_NUMBER()对每个itenid下的记录按主键排序，标记序号后通过自连接关联同一itenid下的第一条和第二条记录，直接获取所需字段。

针对原始表s_flight_offers_segments的查询 #

WITH ranked_segments AS (
    SELECT 
        id AS segmentIdPk,
        itenid,
        departure,
        at_dept,
        arrival,
        at_arr,
        offer_id,
        -- 按itenid分组，按主键id升序标记记录序号
        ROW_NUMBER() OVER (PARTITION BY itenid ORDER BY id ASC) AS seg_rank
    FROM s_flight_offers_segments
    WHERE quoteid = 1
)
SELECT
    d.segmentIdPk,
    d.offer_id,
    d.itenid,
    d.departure,
    d.at_dept,
    a.arrival,
    a.at_arr
FROM ranked_segments d
-- 关联同一itenid下的第二条记录
JOIN ranked_segments a 
    ON d.itenid = a.itenid 
    AND d.seg_rank = 1 
    AND a.seg_rank = 2;

针对测试表结构的查询（关联多表） #

如果基于用户提供的测试表实现需求，查询如下：

WITH ranked_segments AS (
    SELECT 
        s.id AS segmentIdPk,
        i.id AS itenid,
        s.departure,
        s.dept_time AS at_dept,
        s.arrival,
        s.arr_time AS at_arr,
        s.offerId,
        ROW_NUMBER() OVER (PARTITION BY i.id ORDER BY s.id ASC) AS seg_rank
    FROM Segment s
    JOIN Itenary i ON s.offerId = i.offerid AND s.quoteId = i.quoteId
    WHERE s.quoteId = 1
)
SELECT
    d.segmentIdPk,
    d.offerId,
    d.itenid,
    d.departure,
    d.at_dept,
    a.arrival,
    a.at_arr
FROM ranked_segments d
JOIN ranked_segments a 
    ON d.itenid = a.itenid 
    AND d.seg_rank = 1 
    AND a.seg_rank = 2;

思路说明 #

1. 窗口函数分组排序：用ROW_NUMBER()按itenid分组，按主键id升序给每条记录标记序号，确保同一itenid下第一条记录序号为1，第二条为2。
2. 自连接关联数据：将标记为1的出发段记录和标记为2的到达段记录通过itenid关联，直接获取所需字段，保证主键与字段的正确匹配。

内容的提问来源于stack exchange，提问作者Kunal Parekh