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.结果