MySQL之CONCAT，CONCAT_WS，GROUP_CONCAT

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID‘,
  `name` varchar(255) DEFAULT NULL COMMENT ‘名称‘,
  `score` int(11) DEFAULT NULL COMMENT ‘分数‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT=‘学生分数表‘;

INSERT INTO `demo`.`student` (`id`, `name`, `score`) VALUES (‘1‘, ‘小明‘, ‘87‘);
INSERT INTO `demo`.`student` (`id`, `name`, `score`) VALUES (‘2‘, ‘小美‘, ‘98‘);
INSERT INTO `demo`.`student` (`id`, `name`, `score`) VALUES (‘3‘, ‘小爱‘, ‘97‘);
INSERT INTO `demo`.`student` (`id`, `name`, `score`) VALUES (‘4‘, ‘小王‘, ‘97‘);
INSERT INTO `demo`.`student` (`id`, `name`, `score`) VALUES (‘5‘, ‘小爱‘, ‘97‘);
INSERT INTO `demo`.`student` (`id`, `name`, `score`) VALUES (‘6‘, ‘小明‘, ‘87‘);

-- 1.连接字符
SELECT CONCAT(id, name, score) AS info FROM student; 
-- 2.连接字符并且可以在中间加字符
SELECT CONCAT_WS(‘,‘,id, name, score) AS info FROM student;
-- 3.如果中间字符为null，结果为null
SELECT CONCAT_WS(null,id, name, score) AS info FROM student;
-- 4.name相同最小的id
SELECT name, MIN(id) FROM student GROUP BY name;
-- 5.name相同的id
SELECT name, GROUP_CONCAT(id) FROM student GROUP BY name;
-- 6.id号从大到小排序，且用‘_‘作为分隔符：
SELECT name, GROUP_CONCAT(id ORDER BY id DESC SEPARATOR ‘_‘) FROM student GROUP BY name;
-- 7.查询以name分组的所有组的id和score
SELECT name, GROUP_CONCAT(CONCAT_WS(‘-‘,id,score) ORDER BY id) FROM student GROUP BY name;