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

?

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 ts
group by pcode
having count(*)>2

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

?

select * 
from ts 
where pcode in
(select pcode
from ts
group by pcode
having 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)是对列中不为空的行进行计数，

?