对 userid ,game 重复的记录去重,只保留一条
CREATE TABLE `mygame` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`game` varchar(50) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
`create_datetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
DELETE FROM mygame WHERE id in (
SELECT A.id FROM(SELECT userid,game,id from mygame
) A,
(
SELECT userid,game ,max(id) as id from mygame group by game,userid HAVING count(*)>1
)
B
WHERE A.userid = B.userid AND A.game = B.game and A.id < B.id
)