mysql将查询结果集用逗号拼接，按首字母排序

mysql将查询结果集用，拼接，按首字母排序

1.创建表，添加数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for activities
-- ----------------------------
DROP TABLE IF EXISTS `activities`;
CREATE TABLE `activities`  (
  `sell_date` date NULL DEFAULT NULL,
  `product` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of activities
-- ----------------------------
INSERT INTO `activities` VALUES ('2020-05-30', 'Headphone');
INSERT INTO `activities` VALUES ('2020-06-01', 'Pencil');
INSERT INTO `activities` VALUES ('2020-06-02', 'Mask');
INSERT INTO `activities` VALUES ('2020-05-30', 'Basketball');
INSERT INTO `activities` VALUES ('2020-06-01', 'Bible');
INSERT INTO `activities` VALUES ('2020-06-02', 'Mask');
INSERT INTO `activities` VALUES ('2020-05-30', 'T-Shirt');

SET FOREIGN_KEY_CHECKS = 1;

2.要求
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。

3.sql语句
group_concat(product ORDER BY CONVERT(product USING GBK) asc：将product 用逗号拼接，按首字母排序

SELECT sell_date,count(DISTINCT product) num_sold ,group_concat(DISTINCT product  ORDER BY CONVERT(product USING GBK) asc) product FROM `activities` GROUP BY sell_date

4.结果