数据库视图问题

Question

现在有表 Effect 里有字段 id,train_id,CoalType,area,Evaluatedate
表T_PH 里有字段 id,train_id,ph_value,collection_time
表t_flow里字段 id,train_id,flow_value,collection_time
现在要给Effect 写一视图
要把T_PH里的ph_value 和t_flow表里的flow_value加入到视图里去
 
Effect 和 T_PH 的关系 是 两个train_id 匹配 但是train_id 不是唯一字段
也就是说 表t_ph里可能有很多train_id重复的值
现在要根据 t_ph表里的collection_time 按desc排序,然后collection_time 要小于Effect 表里的Evaluatedate然后取结果 集的第一条就能和effect匹配上
表t_flow和t_ph的一样视图不知道怎么写.请各位求救了
PS: Effect 中有 tradin_id重复的值,需要全部查询出来

Answer 1

CREATE OR REPLACE VIEW v_effect as 
  SELECT l.*,m.ph_value, n.flow_value
    FROM (SELECT id, train_id, CoalType, area, Evaluatedate, ph_value
            FROM (SELECT a.*,
                         b.ph_value,
                         row_number() over(PARTITION BY a.train_id ORDER BY b.collection_time DESC) rn
                    FROM effect a, t_ph b
                   WHERE a.train_id = b.train_id AND
                         b.collection_time < a.evaluatedate)
           WHERE rn = 1) m,
         (SELECT id, train_id, CoalType, area, Evaluatedate, flow_value
            FROM (SELECT a.*,
                         b.flow_value,
                         row_number() over(PARTITION BY a.train_id ORDER BY b.collection_time DESC) rn
                    FROM effect a, t_flow b
                   WHERE a.train_id = b.train_id AND
                         b.collection_time < a.evaluatedate)
           WHERE rn = 1) n,effect l
   WHERE m.train_id = n.train_id and m.train_id=l.train_id;

Answer 2

create view v_Effect as 
select a.*,b.ph_value,c.flow_value from  effect a,
(
  select * from t_ph a
  where not exists(select 1 from t_ph b where a.train_id=b.train_id
  and a.collection_time<b.collection_time)
) b,
(
  select * from t_flow a
  where not exists(select 1 from t_flow b where a.train_id=b.train_id
  and a.collection_time<b.collection_time)
) c
where a.train_id=b.train_id and a.train_id=c.train_id

Answer 3

--迟了点 有事情select a.*,b.ph_value,c.flow_value
from Effect a
(select id,train_id,ph_value,collection_time
from
(select id,train_id,ph_value,collection_time,row_number() over(partition by train_id order by collection_time) rn
from T_PH) k
where k.rn=1) b,
(select id,train_id,flow_value,collection_time
from
(select id,train_id,flow_value,collection_time,row_number() over(partition by train_id order by collection_time) rn
from t_flow) k
where k.rn=1) c
where a.train_id=b.train_id and a.train_id=c.train_id and a.Evaluatedate>b.collection_time and a.Evaluatedate>c.collection_time