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