时间戳

首页 > 解决方案 > 如果可用，Oracle 使用 n-2 数据更新 n-1 列，否则为“新”

database - 如果可用，Oracle 使用 n-2 数据更新 n-1 列，否则为“新”

问题描述

我们正在使用 Oracle v12+

我们有一种情况，我们需要将status 列更新为先前的值，只要我们有第RENEW一个ID（它是功能 ID，并且针对一个 ID 有很多行）。

请参阅下面的示例 i/o。数据按每个 ID 的时间戳排序。

我们需要特定的连接来更新吗？为此，我已将数据复制到临时表中，但没有成功。

表名：table_book_status

Input
[id]        [word]  [status]   [timestamp]
B000JMLBHU  book    RENEW
B000JMLBHU  read    RENEW
B000JMLBHU  was     MODIFY
B000JMLBHU  story   ADD
B000R93D4Y  with    RENEW
B000R93D4Y  book    RENEW
B000R93D4Y  story   RENEW
B000R93D4Y  was     MODIFY
B000R93D4Y  have    ADD
B001892DGG  was     ADD
B001892DWA  was     ADD
B001BXNQ2O  was     RENEW
B001BXNQ2O  book    RENEW
B001H55R8M  was     MODIFY
B001HQHCBQ  was     ADD
B001HQHCBQ  story   ADD
B001HQHCBQ  bella   ADD
B001HQHCBQ  with    ADD
B001HQHCBQ  love    ADD
B001HQHCBQ  zsadist ADD


Output
[id]        [word]  [status]    [timestamp]
B000JMLBHU  book    RENEW
B000JMLBHU  read    **MODIFY**
B000JMLBHU  was     MODIFY
B000JMLBHU  story   ADD
B000R93D4Y  with    RENEW
B000R93D4Y  book    RENEW
B000R93D4Y  story   **MODIFY**
B000R93D4Y  was     MODIFY
B000R93D4Y  have    ADD
B001892DGG  was     ADD
B001892DWA  was     ADD
B001BXNQ2O  was     RENEW
B001BXNQ2O  book    **ADD**
B001H55R8M  was     MODIFY
B001HQHCBQ  was     ADD
B001HQHCBQ  story   ADD
B001HQHCBQ  bella   ADD
B001HQHCBQ  with    ADD
B001HQHCBQ  love    ADD
B001HQHCBQ  zsadist ADD

标签： databaseoracleselectoracle12cinsert-update

解决方案

您可以按如下方式使用lag和窗口函数：sum

select id, word, 
       case when sm = 1 and status = 'Renew' 
            then coalesce(lgst, 'Add') 
       else status
       end as status, 
       timestamp 
from
    (select id, word, status, 
            sum(case when status = 'Renew' then 1 else 0 end) over (partition by id order by timestamp) as sm,
            lag(status) over (partition by id order by timestamp) as lgst, timestamp
       from your_table)

推荐阅读