create table TMP (a varchar2(10), b varchar2(10)), c varchar2(10)
insert into tmp select '1','2','3' from dual union select '1','3','3' from dual union select '2','5','3' from dual union select '2','6','3' from dual union select 'a','5','3' from dual union select 'a','6','3' from dual表里多一个C列呢?a, b,c -------------- 1,3,3 2,6,3 a, 6,3
select a,max(b),c from tmp group by a,c order by a
--临时表 DELETE FROM TMP d WHERE (d.a,d.b) NOT IN ( SELECT t.a,max(t.b) FROM TMP t GROUP BY t.a); COMMIT SELECT * FROM TMP
分析函数吧哥们。 select a,row_number(b)over(paition by a order by b) sfrom tmp where s=1 group by a order by a;手写的有问题改改吧。也可以使用delete;另外一个思路了。
如果2列,当然 max 最简单了。。如果 列很多,,那就用分析函数吧。。 select a,b,c from ( select a,b,c,row_number() over(partition by a order by b desc) rn) t where t.rn=1;
select t.* from t, ( select c1,c2,.. from t group by c1,c2,.. having(count(1))>1) t2 where t.c1=t2.c1 and t.c2=t2.c2 ..
推荐分析函数,row_number() over(partition by .. order by ..)
给个例子给你把 delete from table where rowid in( select rowid from ( select rowid, row_number() over(partition by col1,col2col3 order by col)rn from table )where rn>1 ) 差不多就是这样吧。删除重复数据保留第一条数据
第一种: select t.a, max(t.b) from tmp t group by t.a; 第二种:使用分析函数 select a,b from (select t.a, t.b, row_number() over(partition by t.a order by t.b desc) rn from tmp t) t1 where t1.rn = 1; 实际删除SQL: delete from tmp t where (t.a,t.b) not in (select a,b from (select t.a, t.b, row_number() over(partition by t.a order by t.b desc) rn from tmp t) t1 where t1.rn = 1);
delete from TMP where a in (select a from TMP group by a having count(a) > 1) and b not in (select max(b) from TMP group by a having count(a) > 1) select * from TMP;
看到以前自己回复的帖子,心里有点小激动。现在我常用的方法是这样的: DELETE FROM TMP WHERE ROWID IN (SELECT ROWID FROM (SELECT ROWID, ROW_NUMBER() OVER(PARTITION BY D.A ORDER BY D.B) RN FROM TMP D) WHERE RN > 1)
create table TMP
(a varchar2(10),
b varchar2(10)),
c varchar2(10)
insert into tmp
select '1','2','3' from dual
union
select '1','3','3' from dual
union
select '2','5','3' from dual
union
select '2','6','3' from dual
union
select 'a','5','3' from dual
union
select 'a','6','3' from dual表里多一个C列呢?a, b,c
--------------
1,3,3
2,6,3
a, 6,3
DELETE FROM TMP d
WHERE (d.a,d.b) NOT IN (
SELECT t.a,max(t.b) FROM TMP t
GROUP BY t.a);
COMMIT
SELECT * FROM TMP
select a,row_number(b)over(paition by a order by b) sfrom tmp
where s=1
group by a
order by a;手写的有问题改改吧。也可以使用delete;另外一个思路了。
select a,b,c from (
select a,b,c,row_number() over(partition by a order by b desc) rn) t
where t.rn=1;
from t,
( select c1,c2,.. from t group by c1,c2,.. having(count(1))>1) t2
where t.c1=t2.c1
and t.c2=t2.c2
..
delete from table where rowid in(
select rowid from (
select rowid, row_number() over(partition by col1,col2col3 order by col)rn from table
)where rn>1
)
差不多就是这样吧。删除重复数据保留第一条数据
select t.a, max(t.b) from tmp t group by t.a;
第二种:使用分析函数
select a,b
from (select t.a,
t.b,
row_number() over(partition by t.a order by t.b desc) rn
from tmp t) t1
where t1.rn = 1;
实际删除SQL:
delete from tmp t
where (t.a,t.b) not in (select a,b
from (select t.a,
t.b,
row_number() over(partition by t.a order by t.b desc) rn
from tmp t) t1
where t1.rn = 1);
delete from TMP
where a in (select a from TMP
group by a
having count(a) > 1)
and b not in (select max(b)
from TMP
group by a
having count(a) > 1) select * from TMP;
DELETE FROM TMP
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID,
ROW_NUMBER() OVER(PARTITION BY D.A ORDER BY D.B) RN
FROM TMP D)
WHERE RN > 1)