如何修改视图使其从两张不同表获取FLOW_NAME等数据

要让视图的FLOW_NAME同时包含两张表的数据，你可以用UNION ALL（允许重复值）或UNION（自动去重）将原表的查询结果与table2的对应查询结果合并。

前提说明 #

假设你的table2结构与report_checkpoint_history一致，或至少包含原查询用到的核心字段（FLOW_NAME, FLOW_TYPE, DT_CHECKPOINT, DT_FLOW_START, DT_FLOW_END, DURATION, STATUS, HOUR_CHECKPOINT, NUM, time_id）。如果table2缺少某些字段，可直接用NULL填充对应位置。

修改后的视图创建语句 #

CREATE OR REPLACE FORCE EDITIONABLE VIEW LAST_EXEC (
    "FLOW_NAME", "FLOW_TYPE", "DT_CHECKPOINT", "DT_FLOW_START", "DT_FLOW_END", "DURATION", "STATUS", "HOUR_CHECKPOINT", "NUM") 
DEFAULT COLLATION "USING_NLS_COMP"  AS 
-- 原表report_checkpoint_history的查询逻辑：取每日最新time_id的记录
select  
    a.FLOW_NAME, 
    a.FLOW_TYPE, 
    trunc(a.DT_CHECKPOINT) as DT_CHECKPOINT, 
    substr(a.DT_FLOW_START,0,16) as DT_FLOW_START, 
    substr(a.DT_FLOW_END,0,16) as DT_FLOW_END, 
    a.DURATION, 
    a.STATUS, 
    a.HOUR_CHECKPOINT, 
    a.NUM
from report_checkpoint_history a, 
     (select trunc(dt_checkpoint) as trunc_dt_checkpoint,max(time_id) as time_id
      from report_checkpoint_history
      group by trunc(dt_checkpoint)) b 
where trunc(a.dt_checkpoint) = b.trunc_dt_checkpoint and a.time_id = b.time_id

UNION ALL -- 合并table2的查询结果

-- table2的查询逻辑：与原表一致，取每日最新time_id的记录
select  
    c.FLOW_NAME, 
    c.FLOW_TYPE, 
    trunc(c.DT_CHECKPOINT) as DT_CHECKPOINT, 
    substr(c.DT_FLOW_START,0,16) as DT_FLOW_START, 
    substr(c.DT_FLOW_END,0,16) as DT_FLOW_END, 
    c.DURATION, 
    c.STATUS, 
    c.HOUR_CHECKPOINT, 
    c.NUM
from table2 c, 
     (select trunc(dt_checkpoint) as trunc_dt_checkpoint,max(time_id) as time_id
      from table2
      group by trunc(dt_checkpoint)) d 
where trunc(c.dt_checkpoint) = d.trunc_dt_checkpoint and c.time_id = d.time_id

order by DT_CHECKPOINT asc;

关键细节 #

• UNION ALL与UNION的选择：若两张表的FLOW_NAME可能重复且需要去重，改用UNION；若允许重复（或确定无重复），用UNION ALL性能更优。
• 字段缺失处理：如果table2没有原表的某个字段（比如FLOW_TYPE），将对应位置替换为NULL as FLOW_TYPE即可。
• 排序优化：将排序语句移到最后，对合并后的整体结果统一排序，避免多次排序消耗性能。

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