时间戳

首页 > 解决方案 > oracle sql中的合并查询

sql - oracle sql中的合并查询

问题描述

     MERGE INTO temp d
USING (
        select lid,cid,eid,tid,eeid from 
        table where
        eid=5) s
ON (d.lid = s.lid and d.cid = s.cid and d.EID = s.EID and d.tid = s.tid and d.eeid = s.eeid)
WHEN NOT MATCHED THEN INSERT (lid, EID,cid, tid, eeid, is_locked)
VALUES (s.lid, s.EID, s.cid, s.tid  s.eeid, (select nvl(min(is_locked),0) from (
        select ppl.is_locked, ROWNUM RNK
        from ppl
        where ppl.lid = s.lid and ppl.tid = s.id and ppl.cid = s.cid and ppl.eeid = s.eeid and ppl.eid = s.eid
        order by ppl.locked_dt desc
    ) where RNK = 1 ));

即时写入值的选择查询将 s.eid 和所有其他 s 引用称为无效标识符

任何人都可以提供修复我如何在值部分中使用 S 的方法。

标签： sqloracle

解决方案

您可以提前准备值：

MERGE INTO temp d USING (
    select lid,cid,eid,tid,eeid, ppl.is_locked
    from table 
    LATERAL  ( select nvl(min(is_locked), 0) is_locked
                from  ppl
                where
                        ppl.lid = d.lid
                        and ppl.tid = d.id
                        and ppl.cid = d.cid
                        and ppl.eeid = d.eeid
                        and ppl.eid = d.eid
                order by ppl.locked_dt desc
    ) ppl
where eid = 5) s 
ON (d.lid = s.lid and d.cid = s.cid and d.EID = s.EID and d.tid = s.tid and d.eeid = s.eeid)
WHEN NOT MATCHED THEN INSERT (lid, EID, cid, tid, eeid, is_locked)
VALUES (s.lid, s.EID, s.cid, s.tid  s.eeid,s.is_locked);

你在子查询中返回第一行然后 min() 没有意义，所以我得到所有记录中的最小值，如果你真的想要第一行，那么你可以相应地更改子查询，但你明白了

推荐阅读