SQL去重

对 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
)