SQL函数------GROUP_CONCAT()

sql中group_concat()函数完整语法：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

案例1

创建数据库，并插入数据：

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL,
  `score` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '10');
INSERT INTO `score` VALUES ('1', '20');
INSERT INTO `score` VALUES ('1', '30');
INSERT INTO `score` VALUES ('2', '20');
INSERT INTO `score` VALUES ('2', '30');
INSERT INTO `score` VALUES ('2', '40');
INSERT INTO `score` VALUES ('3', '30');
INSERT INTO `score` VALUES ('3', '40');
INSERT INTO `score` VALUES ('3', '40');

执行如下sql 语句：

SELECT id,GROUP_CONCAT(score) from score GROUP BY id;

SELECT id,GROUP_CONCAT(DISTINCT score) from score GROUP BY id;

SELECT id,GROUP_CONCAT(DISTINCT score ORDER BY score DESC) from score GROUP BY id;

SELECT id,GROUP_CONCAT(DISTINCT score ORDER BY score DESC SEPARATOR ";") from score GROUP BY id;

案例二

创建表，并插入数据

-- ----------------------------
-- Table structure for grade1
-- ----------------------------
DROP TABLE IF EXISTS `grade1`;
CREATE TABLE `grade1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuName` varchar(22) DEFAULT NULL,
  `course` varchar(22) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade1
-- ----------------------------
INSERT INTO `grade1` VALUES ('1', '张三', '语文', '91');
INSERT INTO `grade1` VALUES ('2', '张三', '数学', '90');
INSERT INTO `grade1` VALUES ('3', '张三', '英语', '87');
INSERT INTO `grade1` VALUES ('4', '李四', '语文', '79');
INSERT INTO `grade1` VALUES ('5', '李四', '数学', '95');
INSERT INTO `grade1` VALUES ('6', '李四', '英语', '80');
INSERT INTO `grade1` VALUES ('7', '王五', '语文', '77');
INSERT INTO `grade1` VALUES ('8', '王五', '数学', '81');
INSERT INTO `grade1` VALUES ('9', '王五', '英语', '89');

问题：查询语数外三门课的最低分，还有哪个学生考的
方法一：

SELECT 
	course,
	SUBSTRING_INDEX(
		(
			GROUP_CONCAT(stuName ORDER BY score ASC)
		),
		',',
		1
	) as studentName,
	MIN(score)
from grade1 
GROUP BY course;

解析：

• 根据课程course 分组（group by）,min() 函数 求该course 下的最低分
• group_concat() 函数 根据分数从高到底 拼接 stuName
• substring_index() 函数 拆分出 分数最低 stuName
  数据量大的情况下不推荐方法一

方法二：

SELECT 
	a.course,
	a.stuName,
	a.score 
from grade1 a 
where 
	(a.score,a.course) in (
	SELECT 
		MIN(score),
		course 
	from grade1 
	GROUP BY course
	);

解析：
min(score) group by course 可以获取到 course 的最低分，而且知道 course,socre 也可以确定是哪个学生。