表如下:
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 ;