|Rownumber |OldIdassigned |commoncode |
------------------------------------------
| 1 |FLEX |Y2573F102 |
------------------------------------------
| 2 |RCL |Y2573F102 |
------------------------------------------
| 3 |FLEX |Y2573F102 |
------------------------------------------
| 4 |QGEN |N72482123 |
------------------------------------------
| 5 |QGEN |N72482123 |
------------------------------------------
| 6 |QGEN |N72482123 |
------------------------------------------
| 7 |RACE |N72482123 |
------------------------------------------
| 8 |CLB |N22717107 |
------------------------------------------
| 9 |CLB |N22717107 |
------------------------------------------
<b>| 10 |CLB |N22717107 |
我需要删除重复记录的基础上的公共代码和一个条件-如果指定是相同的,然后删除其他不要删除。
例如,Y2573F102有3个重复记录行1,2,3 .不需要删除1,2,只需删除第3行。
发布于 2018-09-26 11:19:50
为此,我喜欢可更新的CTE和窗口函数:
with todelete as (
select t.*,
row_number() over (partition by commoncode order by rownumber) as seqnum
from t
)
delete todelete
where seqnum > 1;发布于 2018-09-26 11:20:31
使用窗口函数row_number,根据您的描述和注释,您似乎需要更改分区子句。
delete t
from
(select t1.*,row_number() over(partition by commoncode order by Rownumber) rn from table t1
)t where rn<>1发布于 2018-09-26 11:20:51
使用ROW_NUMBER():
DELETE t
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY OldIdassigned, commoncode ORDER BY rownumber) AS Seq
FROM table t
) t
WHERE t.seq > 1;编辑:如果您只想检查基于commoncode的复制,那么从PARTITION子句中删除OldIdassigned:
DELETE t
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY commoncode ORDER BY rownumber DESC) AS Seq
FROM table t
) t
WHERE t.seq > 1; https://stackoverflow.com/questions/52516499
复制相似问题