SQL面试题（十五）

问题：

    取得表中除去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);