欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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
相关标签: 数据库