sql - 即使更新了一个子行，如何从子表中获取所有行？

Question

即使其中一个子行已更新/满足条件，我如何从S_ASSET_XA （子表）中获取所有匹配行。父表是S_ASSET。

注意：S_ASSET（父）和S_ASSET_XA（子）是一对多的关系。我的要求是如果父表被更新，输出所有子行，或者如果任何子行被更新，那么也输出所有子行。请帮忙。

我试过这个，但没有用，它只拉更新的子行。

`

SELECT
AST.ROW_ID AS ASSET_ID,
AST.ASSET_NUM      AS ASSET_NUM,
XA.ATTR_NAME     AS ATTR_NAME
FROM S_ASSET AST, S_ASSET_XA XA
WHERE AST.ROW_ID = XA.ASSET_ID
AND (AST.LAST_UPD >= TO_DATE('06-JUL-2020 05:01:57','DD-MON-YYYY HH24:MI:SS')
OR XA.LAST_UPD  >= TO_DATE('06-JUL-2020 05:01:57','DD-MON-YYYY HH24:MI:SS'));

`

标签： sqloracle

Answer 1

几种可能性，例如exists或分析max()：

SELECT AST.ROW_ID AS ASSET_ID, AST.ASSET_NUM  AS ASSET_NUM, XA.ATTR_NAME AS ATTR_NAME
FROM S_ASSET AST
join (select asset_id, attr_name, max(last_upd) over (partition by asset_id) max_upd 
        from S_ASSET_XA) XA 
  on AST.ROW_ID = XA.ASSET_ID
  AND greatest(AST.LAST_UPD, XA.max_UPD) >= timestamp '2020-07-06 05:01:57';

dbfiddle 演示

---

编辑：请检查exists版本：

SELECT AST.ROW_ID AS ASSET_ID, AST.ASSET_NUM  AS ASSET_NUM, XA.ATTR_NAME AS ATTR_NAME
  FROM S_ASSET AST
  join S_ASSET_XA XA on AST.ROW_ID = XA.ASSET_ID
  where AST.LAST_UPD >= timestamp '2020-07-06 05:01:57'
     or exists (select 1 from S_ASSET_XA 
                  where AST.ROW_ID = XA.ASSET_ID 
                    and last_upd >= timestamp '2020-07-06 05:01:57' );

和解决方案max()，其中首先进行连接，最后进行条件：

select ASSET_ID, ASSET_NUM, ATTR_NAME
  from (
    SELECT AST.ROW_ID AS ASSET_ID, AST.ASSET_NUM, XA.ATTR_NAME, ast.last_upd,
           max(xa.last_upd) over (partition by xa.asset_id) max_upd
      FROM S_ASSET AST
      join S_ASSET_XA XA on AST.ROW_ID = XA.ASSET_ID  )
  where greatest(LAST_UPD, max_UPD) >= timestamp '2020-07-06 05:01:57';

小提琴手