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

 

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)是对列中不为空的行进行计数，