使用具有子查询的选择查询创建视图

问题描述：

CREATE OR REPLACE VIEW SAMPLE_VIEW(MISSION_ID,"ESMP TRACK NO","RFPS TRACK NO","RADAR ID") 
AS 
SELECT ESMP.MISSION_ID,ESMP.TRACK_NO,RFPS.RFPS_TRK_NO, 
     (SELECT RADAR_ID 
     FROM MATCHED_TT_DETAILS TT1 
     WHERE TT1.MISSION_ID = ESMP.MISSION_ID 
      AND TT1.TRACK_NO = ESMP.TRACK_NO) 
FROM ESMP_DETAILS ESMP, 
    RFPS_DETAILS RFPS 
WHERE ESMP.MISSION_ID = RFPS.MISSION_ID 
    AND ESMP.TRACK_NO = RFPS.ESMP_TRACK_NO;

我创建了上述视图，并且出现错误。使用具有子查询的选择查询创建视图

ESMP_DETAILS表：

MISSION_ID TRACK_NO 
A   4 
B   5 
C   6

RFPS_DETAILS表：

MISSION_ID  RFPS_TRK_NO 
A    77 
B    88

MATCHED_TT_DETAILS表：

MISSION_ID   RADAR_ID 
A     5 
A     6 
B     4

我要像下面的输出鉴于

MISSION_ID TRACK_NO RFPS_TRK_NO RADAR_ID 
A    4   77   5 
A    4   77   6 
B    5   88   4

CAPSLOCK是不是你的朋友。请停止呼喊。如果你得到一个错误，为什么你没有发布错误信息呢？ – HoneyBadger

而错误是？ –

错误是：\t ORA-01427：单行子查询返回多个行 –

答

试试这个，

CREATE OR REPLACE VIEW SAMPLE_VIEW("MISSION_ID","ESMP TRACK NO","RFPS TRACK NO","RADAR ID") 
AS 
SELECT mat.mission_id, 
     esmp.track_no, 
     (SELECT rfps.rfps_trk_no FROM rfps_details rfps WHERE rfps.mission_id = mat.mission_id), 
     mat.radar_id 
FROM matched_tt_details mat JOIN esmp_details esmp ON mat.mission_id = esmp.mission_id;

为什么仍然使用子查询，而不是只加入三个表？如果'mission_id'有多个'rfps_details'行，这仍然会出错;我不确定你可以从微小的数据样本中认定情况总是如此。 –

在我看来，我有近30列，我必须从10个表中获取数据，并从查找表中获取一些列数据。 –

答

尝试......

CREATE OR replace VIEW sample_view 
AS 
    SELECT ESMP.mission_id, 
     ESMP.track_no, 
     RFPS.rfps_trk_no, 
     TT.radar_id 
    FROM esmp_details ESMP, 
     rfps_details RFPS, 
     matched_tt_details TT 
    WHERE ESMP.mission_id = RFPS.mission_id 
     AND RFPS.mission_id = TT.mission_id

使用具有子查询的选择查询创建视图

相关推荐