MySQL在group by 子句中使用with rollup在最后添加上一条数据统计记录数量

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', '李二', '柚子');