有一张表,主键无约束,插入一些数据后主键有重复,无法重新添加主键约束
id name
1000 AAA
1000 BBB
1000 CCC
1001 DDD
1001 EEE
1002 FFF
1003 GGG
1.写一条sql语句,将重复数据从表中选出来
2.写一条(或一组)sql语句,将重复主键删至只剩一条,如上示例id为1000删2条,id为1001删一条(ps:题目没有对具体哪条作要求)在线等....
分可以再加....
id name
1000 AAA
1000 BBB
1000 CCC
1001 DDD
1001 EEE
1002 FFF
1003 GGG
1.写一条sql语句,将重复数据从表中选出来
2.写一条(或一组)sql语句,将重复主键删至只剩一条,如上示例id为1000删2条,id为1001删一条(ps:题目没有对具体哪条作要求)在线等....
分可以再加....
B表就是你要的那个没有重复记录的了
from test
where rowid != (select max(rowid)
from test b
where b.id = test.id)
where id in
(
select id
from big
group by name having count(id) > 1
)删除Id重复,留下rowid最小的:delete from big
where id in
(
select id
from big
group by id having count(id) > 1
)
and rowid not in
(
select min(rowid)
from big
group by id having count(id )>1
)
where id in
(
select id
from big
group by id having count(id) > 1
)
and rowid not in
(
select min(rowid)
from big
group by id having count(id )>1
)
craete table a select id,name from a group by id;这个表就是你要用的表吧,我实验过,正确,然后再添加主键约束
第一 最简洁的 如果表的数据不是很多 最好重新建立一个表B,将原来的表A的数据拷过来 再用distinct 删除
例子:insert into B select distinct id ,name FROM A
第二 可能就是你想要的解决办法吧.同楼上,使用 rowid
delete from A
where id
in (
select id
from A
group by id
having count(id) > 1
)
and rowid
not in(
select min(rowid)
from A
group by id
having count(id)>1
)
alter table table_name
add constraint PK_name primary key (id,name);2.11楼,7楼的解决方法.