时间戳

首页 > 解决方案 > Oracle SQL - 根据标准删除记录子集

sql - Oracle SQL - 根据标准删除记录子集

问题描述

我有下表

ITEM    LOCATION    EFFECTIVE_DATE  SELLING_RETAIL
00139259    11001   01-JUL-20           2
00139259    11001   08-OCT-19           1
00139259    11001   04-OCT-19           1
00139259    11001   01-JAN-18           2
00139259    11001   04-DEC-17           1
00139259    11001   02-MAR-17           500

我只需要根据降序保留项目、位置和生效日期的三个记录。

所以我留在：

ITEM    LOCATION    EFFECTIVE_DATE  SELLING_RETAIL
00139259    11001   01-JUL-20           2
00139259    11001   08-OCT-19           1
00139259    11001   04-OCT-19           1

其余的都应该被删除。

表中有很多项目和位置，我需要为项目/位置的组合保留 3 条记录

标签： sqloraclesql-delete

解决方案

您想使用ROW_NUMBER分析函数查找最近三行之后的行，然后使用伪列与DELETE语句关联：ROWID

DELETE FROM table_name
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT ROW_NUMBER() OVER (
             PARTITION BY item, location ORDER BY effective_date DESC
           ) AS rn
    FROM   table_name
  )
  WHERE  rn > 3
)

其中，对于您的示例数据：

CREATE TABLE TABLE_NAME ( ITEM, LOCATION, EFFECTIVE_DATE, SELLING_RETAIL ) AS
SELECT '00139259', 11001, DATE '2020-07-01', 2 FROM DUAL UNION ALL
SELECT '00139259', 11001, DATE '2019-10-08', 1 FROM DUAL UNION ALL
SELECT '00139259', 11001, DATE '2019-10-04', 1 FROM DUAL UNION ALL
SELECT '00139259', 11001, DATE '2018-01-01', 2 FROM DUAL UNION ALL
SELECT '00139259', 11001, DATE '2017-12-04', 1 FROM DUAL UNION ALL
SELECT '00139259', 11001, DATE '2017-03-02', 500 FROM DUAL;

然后删除后：

SELECT * FROM table_name;

输出：

物品 地点 EFFECTIVE_DATE SELLING_RETAIL
00139259 11001 2020-07-01 00:00:00 2
00139259 11001 2019-10-08 00:00:00 1
00139259 11001 2019-10-04 00:00:00 1

db<>在这里摆弄

推荐阅读