1. delete from t t1 where 编号 where not exists(select * from (select min(编号)编号 , 类型 from t group by 类型 ) t2 where t1.编号=t2.编号)
1,用distinct做就可以了 select distinct(A) from tablename;2,这么简单的sql不用存储过程了吧,否则简直是牛刀杀鸡啊!
SQL语句: delete from tab1 where 编号 not in(select 编号 from (select min(编号) 编号 ,类型 from tab1 group by 类型)
正解,多了个东东。 delete from t t1 where not exists(select * from (select min(编号)编号 , 类型 from t group by 类型 ) t2 where t1.编号=t2.编号)
这个不用PROC的吧。没有意义 啊
凑和热闹SQL> select id,t,row_number() over(partition by T order by ID) rn from t_21; ID T RN ---------- ---------- ---------- 1 A 1 3 A 2 4 A 3 2 B 1 5 B 2SQL> select * from t_21; ID T ---------- ---------- 1 A 2 B 3 A 4 A 5 BSQL> delete from t_21 a where not exists(select id,t from (select id,t,row_number() over(partition b y T order by ID) rn from t_21) b where rn=1 and a.id=b.id);已删除3行。SQL> select * from t_21; ID T ---------- ---------- 1 A 2 BSQL> commit;提交完成。
delete tab a where rowid>(select min(rowid) from tab where 类型=a.类型)
总结如下:用not exists,not in ,over函数1. delete from t t1 where 编号 where not exists (select * from (select min(编号) 编号, 类型 from t group by 类型) t2 where t1.编号 = t2.编号); 2.delete from t_21 a where not exists (select id, t from (select id, t, row_number() over(partition b y T order by ID) rn from t_21) b where rn = 1 and a.id = b.id);
(select min(编号)编号 , 类型 from t group by 类型 ) t2 where t1.编号=t2.编号)
1,用distinct做就可以了
select distinct(A) from tablename;2,这么简单的sql不用存储过程了吧,否则简直是牛刀杀鸡啊!
delete from tab1
where 编号 not in(select 编号 from (select min(编号) 编号 ,类型 from tab1 group by 类型)
delete from t t1 where not exists(select * from
(select min(编号)编号 , 类型 from t group by 类型 ) t2 where t1.编号=t2.编号)
---------- ---------- ----------
1 A 1
3 A 2
4 A 3
2 B 1
5 B 2SQL> select * from t_21; ID T
---------- ----------
1 A
2 B
3 A
4 A
5 BSQL> delete from t_21 a where not exists(select id,t from (select id,t,row_number() over(partition b
y T order by ID) rn from t_21) b where rn=1 and a.id=b.id);已删除3行。SQL> select * from t_21; ID T
---------- ----------
1 A
2 BSQL> commit;提交完成。
where 编号
where not exists (select *
from (select min(编号) 编号, 类型 from t group by 类型) t2
where t1.编号 = t2.编号); 2.delete from t_21 a
where not exists (select id, t
from (select id,
t,
row_number() over(partition b y T order by ID) rn
from t_21) b
where rn = 1
and a.id = b.id);
welyngj大侠速度太快了。