提问人：user3276905 提问时间：11/17/2023 最后编辑：MT0user3276905 更新时间：11/17/2023 访问量：35
使用 Oracle SQL 查找首次出现和更新
Find first occurrence and update using Oracle SQL
问：
我有以下数据，我想找出每个开始和结束日期的首次出现的付款开始和付款结束日期，并将其标记为已处理。如何找到行并写入和更新sql？
实际表
ID Start Date End Date Pay Begin Pay End Date Processed
123 01/01/2022 01/30/2022 01/01/2023 01/14/2023 N
123 01/01/2022 01/30/2022 01/01/2023 01/14/2023 N
123 02/01/2022 03/30/2022 03/01/2023 03/14/2023 N
123 02/01/2022 03/30/2022 03/01/2023 03/14/2023 N
123 05/01/2022 05/30/2022 05/01/2023 05/14/2023 N
预期结果
ID Start Date End Date Pay Begin Pay End Date Processed
123 01/01/2022 01/30/2022 01/01/2023 01/14/2023 Y
123 01/01/2022 01/30/2022 01/01/2023 01/14/2023 N
123 02/01/2022 03/30/2022 03/01/2023 03/14/2023 Y
123 02/01/2022 03/30/2022 03/01/2023 03/14/2023 N
123 05/01/2022 05/30/2022 05/01/2023 05/14/2023 Y
答：
0赞
MT0
11/17/2023
#1
使用相关子查询（在伪列上关联）和分析函数来查找每个分区中的第一行：ROWIDROW_NUMBER
UPDATE table_name
SET processed = 'Y'
WHERE ROWID IN (
SELECT ROWID
FROM (
SELECT ROW_NUMBER() OVER (
PARTITION BY id, start_date, end_date
ORDER BY pay_begin, pay_end_date
) AS rn
FROM table_name
)
WHERE rn = 1
)
其中，对于示例数据：
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (ID, Start_Date, End_Date, Pay_Begin, Pay_End_Date, Processed ) AS
SELECT 123, DATE '2022-01-01', DATE '2022-01-30', DATE '2023-01-01', DATE '2023-01-14', 'N' FROM DUAL UNION ALL
SELECT 123, DATE '2022-01-01', DATE '2022-01-30', DATE '2023-01-01', DATE '2023-01-14', 'N' FROM DUAL UNION ALL
SELECT 123, DATE '2022-02-01', DATE '2022-03-30', DATE '2023-03-01', DATE '2023-03-14', 'N' FROM DUAL UNION ALL
SELECT 123, DATE '2022-02-01', DATE '2022-03-30', DATE '2023-03-01', DATE '2023-03-14', 'N' FROM DUAL UNION ALL
SELECT 123, DATE '2022-05-01', DATE '2022-05-30', DATE '2023-05-01', DATE '2023-05-14', 'N' FROM DUAL;
然后，在 之后，该表包含：UPDATE
| 编号 | START_DATE | END_DATE | PAY_BEGIN | PAY_END_DATE | 处理 |
|---|---|---|---|---|---|
| 123 | 2022-01-01 00:00:00 | 2022-01-30 00:00:00 | 2023-01-01 00:00:00 | 2023-01-14 00:00:00 | Y |
| 123 | 2022-01-01 00:00:00 | 2022-01-30 00:00:00 | 2023-01-01 00:00:00 | 2023-01-14 00:00:00 | N |
| 123 | 2022-02-01 00:00:00 | 2022-03-30 00:00:00 | 2023-03-01 00:00:00 | 2023-03-14 00:00:00 | Y |
| 123 | 2022-02-01 00:00:00 | 2022-03-30 00:00:00 | 2023-03-01 00:00:00 | 2023-03-14 00:00:00 | N |
| 123 | 2022-05-01 00:00:00 | 2022-05-30 00:00:00 | 2023-05-01 00:00:00 | 2023-05-14 00:00:00 | Y |
评论