连接3个表时复制行

efzxgjgh  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(328)

我想用一个公共id连接3个表,但是我不断得到重复的行,如下所示。

我希望每行每种类型只有一个值。例如,flight,“oneway\u flight”、“roundout\u flight”或“roundin\u flight”应该只有一个值,其余两列应该为null。
下面是所需的输出示例

下面是我在sql中的查询

SELECT o."flight" AS "oneway_flight", 
"ro"."flight" AS "roundout_flight", 
"ri"."flight" AS "roundin_flight", 
o.departure_time AS "oneway_departure_time", 
ro.departure_time AS "roundout_departure_time",    
ri.departure_time AS "roundin_departure_time", 
o.arrival_time AS "oneway_arrival_time", 
ro.arrival_time AS "roundout_arrival_time", 
ri.arrival_time AS "roundin_arrival_time" 
FROM "dbo"."data" "d" 
LEFT JOIN "dbo"."ONEWAY_OUTBOUND" "o" ON d.product_id = o.product_id 
LEFT JOIN "dbo"."ROUNDTRIP_OUTBOUND" "ro" ON d.product_id = ro.product_id 
LEFT JOIN "dbo"."ROUNDTRIP_INBOUND" "ri" ON d.product_id = ri.product_id 
WHERE d.product_id = 'TXXXXXXX'

请让我知道如何修改上面的查询以获得所需的输出(如第二个屏幕截图所示)。
如果有什么不清楚的请告诉我。
感谢你的帮助!

mutmk8jj

mutmk8jj1#

您需要三个独立的查询,连接在一起。

SELECT o."flight" AS "oneway_flight", 
       NULL AS "roundout_flight", 
       NULL."flight" AS "roundin_flight", 
       o.departure_time AS "oneway_departure_time", 
       NULL AS "roundout_departure_time",    
       NULL AS "roundin_departure_time", 
       o.arrival_time AS "oneway_arrival_time", 
       NULL AS "roundout_arrival_time", 
       NULL AS "roundin_arrival_time" 
  FROM "dbo"."data" "d" 
  LEFT JOIN "dbo"."ONEWAY_OUTBOUND" "o" ON d.product_id = o.product_id 
  WHERE d.product_id = 'TXXXXXXX'

 UNION ALL

     SELECT NULL AS "oneway_flight", 
       "ro"."flight" AS "roundout_flight", 
       NULL AS "roundin_flight", 
       NULL AS "oneway_departure_time", 
       ro.departure_time AS "roundout_departure_time",    
       NULL AS "roundin_departure_time", 
       NULL AS "oneway_arrival_time", 
       ro.arrival_time AS "roundout_arrival_time", 
       NULL AS "roundin_arrival_time" 
  FROM "dbo"."data" "d" 
  LEFT JOIN "dbo"."ROUNDTRIP_OUTBOUND" "ro" ON d.product_id = ro.product_id 
  WHERE d.product_id = 'TXXXXXXX'

 UNION ALL

 SELECT NULL AS "oneway_flight", 
       NULL AS "roundout_flight", 
       "ri"."flight" AS "roundin_flight", 
       NULL AS "oneway_departure_time", 
       NULL AS "roundout_departure_time",    
       ri.departure_time AS "roundin_departure_time", 
       NULL AS "oneway_arrival_time", 
       NULL AS "roundout_arrival_time", 
       ri.arrival_time AS "roundin_arrival_time" 
  FROM "dbo"."data" "d" 
  LEFT JOIN "dbo"."ROUNDTRIP_INBOUND" "ri" ON d.product_id = ri.product_id 
  WHERE d.product_id = 'TXXXXXXX'

相关问题