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

/*
Navicat MySQL Data Transfer

Source Server         : 本机
Source Server Version : 50022
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50022
File Encoding         : 65001

Date: 2012-04-24 17:22:41
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `ts`
-- ----------------------------
DROP TABLE IF EXISTS `ts`;
CREATE TABLE `ts` (
  `id` int(11) default NULL,
  `pcode` int(11) default NULL,
  `cno` varchar(20) default NULL,
  `count1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of ts
-- ----------------------------
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字段相同的记录：

select * from ts where pcode in (select pcode from ts group by pcode having count(1)>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字段相同的记录

delete from ts where pcode in (select pcode from ts group by pcode having count(1)>1) and id not in (select min(id)  from ts group by pcode  having count(1) >= 2);

 

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

delete  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函数中的参数有一个为空便会返回空值。