mysql,oracle里删除重复记录,只保留一条(除了自增长字段,其他的字段完全相同为相同记录)
来源:互联网 发布:做淘宝客挣钱吗 编辑:程序博客网 时间:2024/05/17 00:18
//mysql脚本
create table deletetest(
id bigint primary key auto_increment,
name varchar(12) not null,
age int
);
insert into deletetest(name,age) values ('admin',20);
insert into deletetest(name,age) values ('centre',19);
insert into deletetest(name,age) select name,age from deletetest;
--------------------------------------------------------------------------------------------------------------
select distinct name,age from deletetest;//select id from deletetest group by name,age having count(name)>1;
select id from deletetest where id not in (select id from deletetest group by name,age having count(name)>1);
--------------------------------------------------------------------------------------------------------------
//delete操作
delete from deletetest where id =6;
--在mysql中不能指定一个语句来更新数据库中的数据
delete deletetest from deletetest where id in (select id from deletetest group by name,age having count(name)>1);在
mysql中是错误的
--在mysql用自连接,能够解决
delete m1 from deletetest as m1,deletetest as m2 where m1.name=m2.name and m1.age=m2.age and m1.id>m2.id;
//oracle脚本
--建表
create table deletetest(
myid number(10) primary key,
myname varchar2(12) not null,
age integer check (age between 1 and 100)
);
--建序列
-- Create sequence
create sequence deletetest_se
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 50
order;
--建自动更新的触发器
create or replace trigger delete_handle_tri
before insert on deletetest
for each row WHEN (new.myid is null)
declare
-- local variables here
nexthandle number;
begin
select deletetest_se.nextval into :new.myid from dual;
--end ;
end delete_handle_tri;
--插入数据
insert into deletetest(myname,age) values('admin','19');
insert into deletetest(myname ,age) values('centre','20');
insert into deletetest(myname ,age) select myname,age from deletetest;
select distinct myname,age from deletetest;
select myname from (select distinct myname,age from deletetest group by myname,age having count(myname)>1);
//删除重复记录(只有自增长的字段不算入),只保留一条
begin
for r in (select myname,age,max(rowid) rowid_temp from deletetest
group by myname,age having count(myname)>1) loop
delete from deletetest where myname=r.myname
and age=r.age and rowid <> r.rowid_temp ;
commit;
end loop;
end;
end;
- mysql,oracle里删除重复记录,只保留一条(除了自增长字段,其他的字段完全相同为相同记录)
- 删除表中某字段重复的记录(只保留一条)
- SqlServer-删除某一个字段重复的记录,只保留一条
- sql 删除表中多余的重复记录(多个字段),只保留一条记录
- sql 删除表中多余的重复记录(多个字段),只保留一条记录
- sql 删除表中多余的重复记录(多个字段),只保留一条记录
- mysql 查找重复的字段,并删除记录只保留一条
- MySQL按某些字段删除重复记录,只保留一条记录
- mysql 删除字段内的重复内容只保留一条
- oracle 删除重复记录 只保留一条
- MySQL删除重复记录只保留一条
- MySQL删除重复记录只保留一条
- MySQL删除重复记录只保留一条
- MySQL删除重复记录只保留一条
- mysql删除重复记录,只保留一条。
- 删除有两个字段相同的多条重复记录,只保存一条
- 删除有两个字段相同的多条重复记录,只保存一条
- oracle中删除重复数据(几个字段相同的数据为相同记录)
- 擦眼镜的推荐方法
- JPA的Basic注解
- 新浪搜狐房产网站网页显示错误的解决方法
- 尾盘拉升的YM
- LXLT面试题-详解
- mysql,oracle里删除重复记录,只保留一条(除了自增长字段,其他的字段完全相同为相同记录)
- Qt QLabel 变颜色小例子
- 小学教材失实一事体现出僵化的教育思维
- 数据库助手类(SQLHelper)
- Web页面退出前提示用户保存数据
- 综合布线标准(三)
- 2010-10-21
- android学习笔记之3D--(01)-Renderer接口
- http://www.jtben.com