mysql複数列を1列に結合し、1行を複数行に回転

考え方:2列の日付をyyy-MM-dd形式にフォーマットし、2列を1列に結合し、カンマで区切ってから、カンマに基づいて1列の1行を複数行に分割し、グループを再編成します.

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