mysql GROUP_CONCAT使用

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

表内容如上图

先看看group_concat语法： group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

以stuName分组，把score字段的值打印在一行，逗号分隔(默认)

select GROUP_CONCAT(score),stuName from grade1 GROUP BY stuName;

其结果是：

那比如现在要查询出 语数外三门课的最低分，还有哪个学生考的？该怎么写？？

select GROUP_CONCAT(stuName ORDER BY score ASC),
  min(score) as score,
  course 
from 
  grade1 
group by 
  course;  

其结果是：

在结果中的第一列 ，有很多姓名并且以逗号隔开，其实这里的姓名就是按照score 升序排的（GROUP_CONCAT(stuName ORDER BY score ASC)），比如第一行的”王五,张三,李四”,就是按照数学的分数由低到高排序的，所以王五是数学分数最低的，那么我们只需要把这个字符串截取第一个人的名字就可以了，我们使用 SUBSTRING_INDEX

/*SUBSTRING_INDEX以逗号分隔，取第一个值*/
select SUBSTRING_INDEX(GROUP_CONCAT(stuName ORDER BY score ASC),',',1),
  min(score) as score,
  course 
from 
  grade1 
group by 
  course; 

其结果是：

如果有并列最低分只能取到一个学生,可以这样修改，并列最低都可以查出：

SELECT
    stuName,
    score,
    course
FROM
    grade1
WHERE
    (score, course) IN (
        SELECT
            min(score),
            course
        FROM
            grade1
        GROUP BY
            course
    );

第一种如果有并列最低分只能取到一个学生，第二种没问题，不过效率可能会稍差点，数据量少就无所谓了

还有一种写法（不知道效率怎么样）：

SELECT
  g.`id`,g.`course`,g.`score`,g.`stuName`
FROM
  (SELECT
    course,
    SUBSTRING_INDEX(
      GROUP_CONCAT(score
        ORDER BY score ASC),
      ',',
      1
    ) AS score
  FROM
    grade1
  GROUP BY course) AS t
  LEFT JOIN grade1 AS g
    ON (
      t.course = g.`course`
      AND t.score = g.`score`
    )

友情链接http://blog.csdn.net/ys410900345/article/details/44828571

转载于:https://my.oschina.net/u/2322796/blog/1553447