查询重复记录跟删除重复记录

2012-07-30

查询重复记录和删除重复记录查询重复记录和删除重复记录?CREATE table ts(id int(11) not null UNIQUE,pco

查询重复记录和删除重复记录

CREATE table ts(   id int(11) not null UNIQUE,   pcode int(11) default NULL,   cno varchar(20) default null,   count1 int(11) default null)INSERT INTO `ts` VALUES ('1', '1', ' 001', '10000');  INSERT INTO `ts` VALUES ('2', '1', ' 002', '5000');  INSERT INTO `ts` VALUES ('3', '1', ' 003', '20000');  INSERT INTO `ts` VALUES ('4', '2', ' 001', '40000');  INSERT INTO `ts` VALUES ('5', '2', ' 003', '30000');  INSERT INTO `ts` VALUES ('6', '3', ' 002', '90000');  INSERT INTO `ts` VALUES ('7', '3', ' 002', '90000');  INSERT INTO `ts` VALUES ('8', '3', ' 002', '90000');

??查询pcode字段相同2条以上的记录:

select *from tsgroup by pcodehaving count(*)>2

查询重复记录跟删除重复记录

?查询pcode字段相同的记录：

select * from ts where pcode in(select pcodefrom tsgroup by pcodehaving count(*)>1)

? 查询重复记录跟删除重复记录

查询pcode,cno,count1三个字段均相同的记录：

select *  from ts where concat(pcode,cno,count1) in (select concat(pcode,cno,count1)from ts group by pcode,cno,count1  having count(1) >= 2)

查询重复记录跟删除重复记录

删除多余的pcode字段相同的记录

SELECT *from ts where pcode in (select pcode from ts group by pcode having count(*)>1) and id not in #查出所有相同的记录排除第一条，将全部删除掉(select min(id)  from ts group by pcode  having count(*) >= 2);

查询重复记录跟删除重复记录

删除多余的pcode,cno,count1三个字段均相同的记录：

SELECT *from ts where  concat(pcode,cno,count1) in (select concat(pcode,cno,count1) from ts group by pcode,cno,count1 having count(1) >= 2) and id not in (select min(id)  from ts group by pcode,cno,count1  having count(1) >= 2);

查询重复记录跟删除重复记录

??在此注意：多字段的时候该语句仅适用于字段值均不为空的情况，concat函数中的参数有一个为空便会返回空值。

1.任何情况下Select COUNT(*) FROM xxx 是最优选择；
2.尽量减少Select COUNT(*) FROM xxx Where COL = ‘xxx’ 这种查询；
3.杜绝Select COUNT(COL) FROM tablename Where COL = ‘xxx’ 的出现。(其中COL非主键)

（1）count(*)是对行数目进行计数

（2）count(column_name)是对列中不为空的行进行计数，

热点排行

其他数据库

查询重复记录跟删除重复记录