mysql删除重复数据

浏览数：20 / 时间：2015年06月12日

场景：
有一个活动成员表，有活动Id和成员Id列，要求处理掉重复的成员，即同一活动下，去掉活动Id和成员Id重复的记录。
表结构：

-- ----------------------------
-- Table structure for `MEMBER`
-- ----------------------------
DROP TABLE IF EXISTS `MEMBER`;
CREATE TABLE `MEMBER` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ACTIVITY_ID` int(11) DEFAULT NULL,
  `USER_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_Reference_32` (`ACTIVITY_ID`) USING BTREE,
  KEY `INDEX_ACTMEB_USER` (`USER_ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1448271 DEFAULT CHARSET=utf8

插入数据：

-- ----------------------------
-- Records of MEMBER
-- ----------------------------
INSERT INTO `MEMBER` VALUES (‘1‘, ‘1‘, ‘11001‘);
INSERT INTO `MEMBER` VALUES (‘2‘, ‘1‘, ‘11001‘);
INSERT INTO `MEMBER` VALUES (‘3‘, ‘1‘, ‘37001‘);
INSERT INTO `MEMBER` VALUES (‘4‘, ‘2‘, ‘37002‘);
INSERT INTO `MEMBER` VALUES (‘5‘, ‘2‘, ‘37002‘);
INSERT INTO `MEMBER` VALUES (‘6‘, ‘1‘, ‘37001‘);

查找多余的重复数据：

SELECT m1.*    FROM MEMBER m1,(
  SELECT ID,ACTIVITY_ID,USER_ID  FROM MEMBER  GROUP BY ACTIVITY_ID,USER_ID  HAVING COUNT(1) > 1
) AS m2
WHERE 
m1.ACTIVITY_ID = m2.ACTIVITY_ID  
AND m1.USER_ID = m2.USER_ID
AND m1.ID <> m2.ID

删除多余的重复数据：

DELETE m1  FROM MEMBER m1,(
  SELECT ID,ACTIVITY_ID,USER_ID  FROM MEMBER  GROUP BY ACTIVITY_ID,USER_ID  HAVING COUNT(1) > 1
) AS m2
WHERE 
m1.ACTIVITY_ID = m2.ACTIVITY_ID  
AND m1.USER_ID = m2.USER_ID
AND m1.ID <> m2.ID

mysql删除重复数据,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

mysql删除重复数据