SQL面试题（十一）

表如下:

a b c 三个字段有可能重复
数据如下:
a b c d e
------- ---------- ---------- ----------- -----------
a a a 1 2
a a a 1 2
a a a 1 2
a a a 1 2
a a a 1 2
b b b 3 3
b b b 3 3
b b b 3 3
b b b 3 3
b b b 3 3
b b b 3 3
b b b 3 3
c c c 4 4
d d d 5 5
d d d 5 5

 

建表：

DROP TABLE IF EXISTS `samedatas`;
CREATE TABLE `samedatas` (
  `Id` int(11) NOT NULL auto_increment,
  `a` varchar(11) default NULL,
  `b` varchar(11) default NULL,
  `c` varchar(11) default NULL,
  `d` int(11) default NULL,
  `e` int(11) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=ujis;

#
# Dumping data for table samedatas
#

INSERT INTO `samedatas` VALUES (1,'a','a','a',1,2);
INSERT INTO `samedatas` VALUES (2,'a','a','a',1,2);
INSERT INTO `samedatas` VALUES (3,'a','a','c',1,2);
INSERT INTO `samedatas` VALUES (4,'a','b','a',1,2);
INSERT INTO `samedatas` VALUES (5,'a','a','a',1,2);
INSERT INTO `samedatas` VALUES (6,'b','b','b',3,3);
INSERT INTO `samedatas` VALUES (7,'b','b','b',3,3);
INSERT INTO `samedatas` VALUES (8,'b','a','b',3,3);
INSERT INTO `samedatas` VALUES (9,'b','b','b',3,3);
INSERT INTO `samedatas` VALUES (10,'b','b','c',3,3);
INSERT INTO `samedatas` VALUES (11,'b','b','c',3,3);
INSERT INTO `samedatas` VALUES (12,'b','b','b',3,3);
INSERT INTO `samedatas` VALUES (13,'c','c','c',4,4);
INSERT INTO `samedatas` VALUES (14,'d','d','d',5,5);
INSERT INTO `samedatas` VALUES (15,'d','d','d',5,5);

 

问题一:

 现在要把三个字段有重复的数据提出来.

Sql:

SELECT a, b, c, COUNT(*) FROM samedatas GROUP BY a, b, c HAVING COUNT(*) > 1;

 

问题二: 合并表中的数据,比如
a a a 1 2
a a a 1 2
a a a 1 2
a a a 1 2
a a a 1 2
这五条纪录要合并成一条
a a a 5 10

 

Sql:

 

SELECT a, b, c, SUM(d), SUM(e) FROM samedatas GROUP BY a, b, c ;