如何简化下面的内部联接SQL查询

Question

我从不同的表中获取记录，并将这样的值连接起来，这样我就可以显示Flight1、Flight2、Flight3和Flight4.作为纵队。由于我使用的是相同的条件，有任何方法来简化这个查询吗？

SELECT b.BookingID

,(SELECT TOP 1 CONCAT(il.CCode,il.FlightNumber,';',dc.[Name])
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD
             ) AS Flight1

,(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name]))
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD
             OFFSET 1 ROWS
        FETCH NEXT 1 ROWS ONLY) AS Flight2

,(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name]))
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD
             OFFSET 2 ROWS
        FETCH NEXT 1 ROWS ONLY) AS Flight3

FROM Booking b
WHERE b.RecordLocator='1234'

谢谢

Answer 1

您可以在顶部使用CTE，主查询可能如下所示。

;with CTE as 
(SELECT CONCAT((il.CCode,il.FlightNumber,';',dc.[Name])) as DisplayName
        FROM ILeg il
        INNER JOIN PLeg pjl ON pjl.InventoryLegID = il.InventoryLegID
             INNER JOIN PSegment pjs ON pjs.SegmentID = pjl.SegmentID
             INNER JOIN BPassenger bp ON bp.PassengerID = pjs.PassengerID
             INNER JOIN City dc ON dc.CityCode = il.DepartureStation
             INNER JOIN City ac ON ac.CityCode = il.ArrivalStation
             WHERE bp.BookingID =  b.BookingID AND il.CarrierCode = '12T'
             ORDER BY il.STD),
SELECT b.BookingID
,(SELECT TOP 1 * from CTE) AS Flight1
,(SELECT TOP 1 * from CTE OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS Flight2
,(SELECT TOP 1 * from CTE OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS Flight3
FROM Booking b
WHERE b.RecordLocator='1234'