sql
SELECT name, count(fruit),GROUP_CONCAT(fruit) as fruits from person GROUP BY name WITH ROLLUP;
正常查询结果如图
使用WITH ROLLUP之后如图,这是字符串的合并,
还可以是数字的合并
附上建表和sql语句
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`fruit` varchar(50) DEFAULT NULL COMMENT '喜欢吃的水果',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', '张三', '芒果');
INSERT INTO `person` VALUES ('2', '张三', '西瓜');
INSERT INTO `person` VALUES ('3', '张三', '苹果');
INSERT INTO `person` VALUES ('4', '张三', '香蕉');
INSERT INTO `person` VALUES ('5', '张三', '柚子');
INSERT INTO `person` VALUES ('6', '李四', '芒果');
INSERT INTO `person` VALUES ('7', '李四', '橘子');
INSERT INTO `person` VALUES ('8', '李四', '葡萄');
INSERT INTO `person` VALUES ('9', '李四', '西瓜');
INSERT INTO `person` VALUES ('10', '李四', '苹果');
INSERT INTO `person` VALUES ('11', '李四', '香蕉');
INSERT INTO `person` VALUES ('12', '王五', '柚子');
INSERT INTO `person` VALUES ('13', '卡特', '芒果');
INSERT INTO `person` VALUES ('14', '王五', '芒果');
INSERT INTO `person` VALUES ('15', '王多鱼', '芒果');
INSERT INTO `person` VALUES ('16', '王五', '西瓜');
INSERT INTO `person` VALUES ('17', '王五', '苹果');
INSERT INTO `person` VALUES ('18', '王五', '香蕉');
INSERT INTO `person` VALUES ('19', '李二', '柚子');