问题:
取得表中除去id外重复的数据
建表:
DROP TABLE IF EXISTS `samedata`;
CREATE TABLE `samedata` (
`Id` int(11) NOT NULL auto_increment,
`name` varchar(12) default NULL,
`object` varchar(12) default NULL,
`info` int(11) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `samedata` VALUES (1,'A','love',90);
INSERT INTO `samedata` VALUES (2,'B','love',90);
INSERT INTO `samedata` VALUES (3,'A','love',90);
INSERT INTO `samedata` VALUES (4,'A','hate',90);
INSERT INTO `samedata` VALUES (5,'C','love',90);
INSERT INTO `samedata` VALUES (6,'A','love',90);
SQL:
SELECT * FROM samedata WHERE Id IN (
SELECT DISTINCT a.id FROM samedata AS b, samedata AS a
WHERE a.name = b.name AND a.object = b.object AND a.id>b.id);2.
SELECT * FROM samedata WHERE Id NOT IN (
SELECT MIN(b.Id) from samedata AS b GROUP BY b.name, b.object, b.info);