还想请教一下 b表结构 id a b c d e 1 x1 x2 x3 ... 2 x1 x2 x3 ... 3 x1 x4 x3 ... 4 x5 x6 x7 ... 5 x5 x6 x7 ...我把每次备分出来的同一表导到一个b表里面,但每次备分出来的数据会存在上次备分出来的数据, 怎样将这些重复的数据删掉只保留一次
delete from t where rowid in ( select row_id from ( select rowid row_id, row_number() over (partition by id order by rowid) rn from t ) where rn <> 1 帮忙解释一下这语句,row_number() over (partition by id order by rowid) rn 是什么意思
意思就是按id分组,举个例子,表table1 id tr 1 10 2 11 1 4 2 6 select row_number() over (partition by id order by tr) xh,id,tr from table1 显示结果: xh id tr 1 1 4 2 1 10 1 2 6 2 2 11
虽然这里没有用显示的ORDER BY。但应该也会排序成over中的形式。 即 xh id tr 1 1 4 1 2 6 2 1 10 2 2 11 其实 delete from t where rowid in ( select row_id from ( select rowid row_id, row_number() over (partition by id order by rowid) rn from t ) where rn <> 1 是对于重复的记录,保留一条,多余的删除。这样的话,应该要保证表t是没有主键的。 也可以用下面的sql来替代,可能比较容易懂点 delete from t a where rowid> ( select min(rowid) from t b where a.id=b.id )
b表结构
id a b c d e
1 x1 x2 x3 ...
2 x1 x2 x3 ...
3 x1 x4 x3 ...
4 x5 x6 x7 ...
5 x5 x6 x7 ...我把每次备分出来的同一表导到一个b表里面,但每次备分出来的数据会存在上次备分出来的数据,
怎样将这些重复的数据删掉只保留一次
select row_id from (
select rowid row_id,
row_number() over (partition by id order by rowid) rn
from t
)
where rn <> 1
帮忙解释一下这语句,row_number() over (partition by id order by rowid) rn 是什么意思
id tr
1 10
2 11
1 4
2 6
select row_number() over (partition by id order by tr) xh,id,tr from table1
显示结果:
xh id tr
1 1 4
2 1 10
1 2 6
2 2 11
即
xh id tr
1 1 4
1 2 6
2 1 10
2 2 11
其实
delete from t where rowid in (
select row_id from (
select rowid row_id,
row_number() over (partition by id order by rowid) rn
from t
)
where rn <> 1 是对于重复的记录,保留一条,多余的删除。这样的话,应该要保证表t是没有主键的。
也可以用下面的sql来替代,可能比较容易懂点
delete from t a where
rowid>
(
select min(rowid)
from t b
where a.id=b.id
)