MYSQL GROUP_CONCAT函数

mysql 中 group_concat() 函数 完整语法

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

例子一：

创建数据库，并插入数据

-- ----------------------------
-- 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');

效果如下图：

 

捕获.PNG

执行如下sql 语句：
SELECT id,GROUP_CONCAT(score) from score GROUP BY id;

捕获.PNG

 

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

捕获.PNG

 

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

捕获.PNG

 

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

捕获.PNG

 

例子二：

创建表，并插入数据

-- ----------------------------
-- 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');

捕获.PNG

问题 查询语数外 三门课的最低分，还有哪个学生考的
方法一：
SELECT course,SUBSTRING_INDEX((GROUP_CONCAT(stuName ORDER BY score ASC)),',',1) as studentName,MIN(score) from grade1 GROUP BY course;

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

解析：

1. 根据课程course 分组（group by）,min() 函数 求该course 下的最低分
2. group_concat() 函数 根据分数从高到底 拼接 stuName
3. 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);

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
    );

捕获.PNG

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