group_concat()函数的参数是可以直接使用order by排序,下面通过例子来说明,首先看下面的t1表。
比如,我们要查看每个人的多个分数,将该人对应的多个分数显示在一起,分数要从高到底排序。可以这样写:
SELECT username,GROUP_CONCAT(score ORDER BY score DESC) AS myScore FROM t1 GROUP BY username;
效果如下:
SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for demo -- ---------------------------- DROP TABLE IF EXISTS `demo`; CREATE TABLE `demo` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `score` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 45 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of demo -- ---------------------------- INSERT INTO `demo` VALUES (1, '张三14', 60); INSERT INTO `demo` VALUES (2, '张三14', 54); INSERT INTO `demo` VALUES (15, '张三14', 61); INSERT INTO `demo` VALUES (16, '张三15', 12); INSERT INTO `demo` VALUES (17, '张三16', 54); INSERT INTO `demo` VALUES (18, '张三17', 33); INSERT INTO `demo` VALUES (41, '张三', 54); INSERT INTO `demo` VALUES (42, '张三', 89); INSERT INTO `demo` VALUES (43, '张三', 48); INSERT INTO `demo` VALUES (44, '张三', 98); SET FOREIGN_KEY_CHECKS = 1;
mysql 中 group_concat(),手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。
我们想根据name分组,然后根据score倒序排序,下面展示两种写法;
sql一:错误排序语句:
SELECT name,GROUP_CONCAT(score ORDER BY score DESC) AS myScore FROM demo GROUP BY name
这种是错误的下面的不是根据GROUP_CONCAT拼接的数据是不会排序的
sql二:正确排序语句:
SELECT name,GROUP_CONCAT(score ORDER BY score DESC) AS myScore FROM demo GROUP BY name order by myScore desc;
必须根据排序后的字段二次排序,第一次只能是排序GROUP_CONCAT里边的如果没有分组的是无法排序的,只能分完组后再根据myScore排序
发表评论(对文章涉及的知识点还有疑问,可以在这里留言,老高看到后会及时回复的。)