mysql 多列合并一列，一行转多行-蒲公英云

mysql 多列合并一列，一行转多行

小灰灰 2022-11-26 11:55 415阅读 0赞

思路：将两列日期先格式化成以yyyy-MM-dd格式，再将两列合并成一列，并且以逗号分隔开，再根据逗号将一列中的一行分割成多行，再分组去重

CREATE TABLE `t_product` (
  `id` varchar(225) NOT NULL,
  `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_product
-- ----------------------------
INSERT INTO `t_product` VALUES ('1', '2020-08-11 19:20:31', '2020-08-11 22:20:36');
INSERT INTO `t_product` VALUES ('2', '2020-08-12 19:09:09', '2020-08-12 22:20:36');
INSERT INTO `t_product` VALUES ('3', '2020-08-13 19:09:14', '2020-08-13 22:20:36');
INSERT INTO `t_product` VALUES ('4', '2020-08-11 19:06:59', '2020-08-14 22:20:36');
SELECT * FROM (
    SELECT     substring_index(
        substring_index(
            all_date,
            ',',
            b.help_topic_id + 1
        ),
        ',' ,- 1
    ) AS all_date_group FROM (
        select concat(DATE_FORMAT(p.start_time,'%Y-%m-%d'), ',', DATE_FORMAT(p.end_time,'%Y-%m-%d')) as all_date FROM t_product p
    ) pp JOIN mysql.help_topic b ON b.help_topic_id < (
        length(all_date) - length(
            REPLACE (all_date, ',', '')
        ) + 1
    )
) ss GROUP BY ss.all_date_group