mysql 多列合并一列,一行转多行
程序员文章站
2022-03-31 22:09:08
...
思路:将两列日期先格式化成以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