UC知道sql删除重复记录
来源：百度知道 编辑：UC知道 时间：2024/06/25 07:29:59
删除数据库中的重复记录，一条sql语句
create database zyj080927;
use zyj080927;
create table a
(
bm char(4),
mc varchar(20)
);
insert into a values(1111,1111);
insert into a values('1112','1111');
insert into a values('1113','1111');
insert into a values('1114','1111');
insert into a values('1111','1111');
insert into a values('1112','1111');
insert into a values('1113','1111');
insert into a values('1114','1111');
SQL语句怎么写??
create database zyj080927;
use zyj080927;
create table a
(
bm char(4),
mc varchar(20)
);
insert into a values(1111,1111);
insert into a values('1112','1111');
insert into a values('1113','1111');
insert into a values('1114','1111');
insert into a values('1111','1111');
insert into a values('1112','1111');
insert into a values('1113','1111');
insert into a values('1114','1111');
SQL语句怎么写??
你看下可以不可以
思路是这样的: 创建临时表,过滤重复值插入临时表,清空原表,插入临时表.
select * into #a from a where 1=2
insert into #a select distinct * from a
truncate table a
insert into a select * from #a
drop table #a
使用distinct可以轻松的去掉重复值
select distinct 想去掉重复值字段 from 表 where 条件
如果是想删除数据库里的重复值就在上面的语句外嵌套1更新语句即可
你看这个方案能不能接受
CREATE TABLE b AS
SELECT DISTINCT * FROM a;
DROP TABLE a;
RENAME TABLE b TO a;
如果要删除重复记录.则必须是按照某一个字段或者某一些字段的值重复取删除,
在a表中加一个自动增加列，如ID，然后使用语句
delete FROM a WHERE ID IN (SELECT MAX(ID) FROM a AS a_1 GROUP BY bm)