(一)单个字段 1、查找表中多余的重复记录，根据(question_title)字段来判断 代码如下 select* from questions where question_title in (select question_title from peoplegroup byquestion_title having count(question_title) 1) 2、删除表中多余的重复记
(一)单个字段
1、查找表中多余的重复记录，根据(question_title)字段来判断
代码如下
select * from questions where question_title in (select question_title from peoplegroup by question_title having count(question_title) > 1)
2、删除表中多余的重复记录，根据(question_title)字段来判断，只留有一个记录
代码如下
delete from questions
where peopleid in (select peopleid from people group by peopleid having count(question_title) > 1)
and min(id) not in (select question_id from questions group by question_title having count(question_title)>1)
(二)多个字段
删除表中多余的重复记录(多个字段)，只留有rowid最小的记录
代码如下
delete from questions where (questions_title,questions_scope) in (select questions_title,questions_scope from questions group by questions_title,questions_scope having count(*) > 1) and question_id not in (select min(question_id) from questions group by questions_scope,questions_title having count(*)>1)
用上述语句无法删除，，创建了临时表才删的，求各位达人解释一下。
代码如下
create table tmp as select question_id from questions where (questions_title,questions_scope) in (select questions_title,questions_scope from questions group by questions_title,questions_scope having count(*) > 1) and question_id not in (select min(question_id) from questions group by questions_scope,questions_title having count(*)>1);
delete from questions where question_id in (select question_id from tmp);
drop table tmp;
(三) 存储过程
代码如下
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
例,
数据库版本 server version: 5.1.41-community-log mysql community server (gpl)
例1，表中有主键(可唯一标识的字段)，且该字段为数字类型
例1测试数据
代码如下
/* 表结构 */
drop table if exists `t1`;
create table if not exists `t1`(
`id` int(1) not null auto_increment,
`name` varchar(20) not null,
`add` varchar(20) not null,
primary key(`id`)
)engine=innodb;
/* 插入测试数据 */
insert into `t1`(`name`,`add`) values
('abc',"123"),
('abc',"123"),
('abc',"321"),
('abc',"123"),
('xzy',"123"),
('xzy',"456"),
('xzy',"456"),
('xzy',"456"),
('xzy',"789"),
('xzy',"987"),
('xzy',"789"),
('ijk',"147"),
('ijk',"147"),
('ijk',"852"),
('opq',"852"),
('opq',"963"),
('opq',"741"),
('tpk',"741"),
('tpk',"963"),
('tpk',"963"),
('wer',"546"),
('wer',"546"),
('once',"546");
select * from `t1`;
+----+------+-----+
| id | name | add |
+----+------+-----+
| 1 | abc | 123 |
| 2 | abc | 123 |
| 3 | abc | 321 |
| 4 | abc | 123 |
| 5 | xzy | 123 |
| 6 | xzy | 456 |
| 7 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789 |
| 10 | xzy | 987 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147 |
| 14 | ijk | 852 |
| 15 | opq | 852 |
| 16 | opq | 963 |
| 17 | opq | 741 |
| 18 | tpk | 741 |
| 19 | tpk | 963 |
| 20 | tpk | 963 |
| 21 | wer | 546 |
| 22 | wer | 546 |
| 23 | once | 546 |
+----+------+-----+
rows in set (0.00 sec)
查找id最小的重复数据(只查找id字段)
代码如下
/* 查找id最小的重复数据(只查找id字段) */
select distinct min(`id`) as `id`
from `t1`
group by `name`,`add`
having count(1) > 1;
+------+
| id |
+------+
| 1 |
| 12 |
| 19 |
| 21 |
| 6 |
| 9 |
+------+
rows in set (0.00 sec)
查找所有重复数据
代码如下
/* 查找所有重复数据 */
select `t1`.*
from `t1`,(
select `name`,`add`
from `t1`
group by `name`,`add`
having count(1) > 1
) as `t2`
where `t1`.`name` = `t2`.`name`
and `t1`.`add` = `t2`.`add`;
+----+------+-----+
| id | name | add |
+----+------+-----+
| 1 | abc | 123 |
| 2 | abc | 123 |
| 4 | abc | |
| 6 | xzy | 456 |
| 7 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147 |
| 19 | tpk | 963 |
| 20 | tpk | 963 |
| 21 | wer | 546 |
| 22 | wer | 546 |
+----+------+-----+
rows in set (0.00 sec)
.syntaxhighlighter{padding-top:20px;padding-bottom:20px;}【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！