sql - 最近 24 小时使用 Oracle SQL 从表中更新记录？

Question

我有一个从 3 个表中提取所有记录的 Pl/SQL 查询。那很好。现在我想从 2 个表(tbl_constit、tbl_email)中提取最后 24 条更新的记录。

下面看实际查询

SELECT DISTINCT c.constit_id AS constitid,
               REPLACE (c.in_labelname, 'None', '') AS fullname,
               c.firstname AS firstname, c.lastname AS lastname,
               c.indiv_title AS title, e.e_addr AS email,
              'InActive' AS status                               
          FROM tbl_constit cn, tbl_email e,tbl_catcod s
         WHERE c.constit_id = e.constit_id
           AND c.constit_id = s.constit_id(+)
           AND e.e_type = 'EMAIL'
           AND e.e_default = '1'
           AND s.cat_code IN ('SPEMU', 'SPENM')              
      ORDER BY c.constit_id;

表 tbl_constit cn，tbl_email e 有“CHGD_DT”字段。

当记录更新时，这个日期字段会改变。 现在如何使用“CHGD_DT”字段从 tbl_constit cn 或 tbl_email 中提取最近 24 条更新的记录？

两个表中的任何一个都可能发生变化。

Answer 1

SELECT DISTINCT c.constit_id AS constitid,
               REPLACE (c.in_labelname, 'None', '') AS fullname,
               c.firstname AS firstname, c.lastname AS lastname,
               c.indiv_title AS title, e.e_addr AS email,
              'InActive' AS status                               
          FROM tbl_constit cn, tbl_email e,tbl_catcod s
         WHERE c.constit_id = e.constit_id
           AND c.constit_id = s.constit_id(+)
           AND e.e_type = 'EMAIL'
           AND e.e_default = '1'
           AND s.cat_code IN ('SPEMU', 'SPENM')
           AND ((cn.chgd_dt > (SYSDATE - 1)) OR (e.chgd_dt > (SYSDATE - 1)))
      ORDER BY c.constit_id;

这将显示在过去 24 小时内更新了 cn 或 e 的所有行，如果要显示更新了两者的行，请将内部 OR 替换为 AND。