Oracle CTE/子问题分解删除

Question

WITH RemoveDate As (
SELECT table.*, ROW_NUMBER()
OVER (PARTITION BY id order by row_added_dttm) AS RowNumber 
FROM table
WHERE (grp = '124') 
and row_added_dttm <= (SELECT MAX(row_added_dttm) from table
 where (grp = '124') )
) 
delete from RemoveDate where RowNumber >1 

您好，我需要在Oracle SQL开发人员上运行它，但它不起作用。我有重复的内容，我需要删除一个，另一个必须仍在数据库中。OFC并非全部都是重复的，也需要删除它们。我只需要一个ID的最新行。

Answer 1

使用 MERGE

sql小提琴

Oracle 11G R2架构设置：

CREATE TABLE table_name ( id, row_added_dttm ) AS
  SELECT 1, DATE '2017-09-20' FROM DUAL UNION ALL
  SELECT 1, DATE '2017-09-19' FROM DUAL UNION ALL
  SELECT 1, DATE '2017-09-18' FROM DUAL UNION ALL
  SELECT 1, DATE '2017-09-17' FROM DUAL UNION ALL
  SELECT 2, DATE '2017-09-20' FROM DUAL UNION ALL
  SELECT 2, DATE '2017-09-18' FROM DUAL UNION ALL
  SELECT 3, DATE '2017-09-15' FROM DUAL;
MERGE INTO table_name t
USING (
  SELECT ROW_NUMBER() OVER ( PARTITION BY id ORDER BY row_added_dttm DESC )
           AS RN,
         ROWID AS rid
  FROM   table_name
) m
ON ( t.ROWID = m.ROWID )
WHEN MATCHED THEN
  UPDATE SET id = id
  DELETE WHERE m.RN > 1;

查询1 ：

SELECT *
FROM   table_name

结果：

| ID |       ROW_ADDED_DTTM |
|----|----------------------|
|  1 | 2017-09-20T00:00:00Z |
|  2 | 2017-09-20T00:00:00Z |
|  3 | 2017-09-15T00:00:00Z |