使用mysql 要处理 带逗号的字符串，比如： “1,2,3,4,5”，变成一列一列

1

2

3

4

5

处理：

  网上搜了下，查了下，没有直接可以用的方法，都是用help_topic表来处理的。使用help_topic时为了解决行转列的问题。

常见的是：

 SELECT 
 SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
 mysql.help_topic 
WHERE 
 help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

扔到本地执行，报错：

错误代码： 1142
SELECT command denied to user 'myDatabase' for table 'help_topic'

去本地mysql 查了下  help_topic  表格

SELECT * FROM help_topic;

 对应的是h_id 。 重新修改上面的语句：

 SELECT 
 SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',h_id+1),',',-1) AS num 
FROM 
  help_topic 
WHERE 
 h_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

得到想要的结果。

实际使用

 环节信息表：

CREATE TABLE `tache_info`  (
  `tch_id` bigint(20) NOT NULL COMMENT '环节id', 
  `person` varchar(4000) COMMENT '处理人员。多个逗号隔开',
  `created_date` datetime NULL DEFAULT NULL COMMENT '环节创建时刻', 
  `finish_date` datetime NULL DEFAULT NULL COMMENT '环节结束时刻',
  PRIMARY KEY (`tch_id`)
) COMMENT = '环节信息表' ;

要求： 根据人员，统计环节滞留情况： 滞留环节总数、平均滞留时长（总滞留时长/总环节数）

处理思路：

        1，人员处理：人员是tache_info 里面的person字段，里面可能是多个人员，多个的用逗号隔开，需要行换列，用上面那个办法。

        2，计算滞留时长： 滞留的话，finish_date 是空的，用 “当前时间-环节创建时间”：

mysql 计算时间差-到秒

 SELECT TIMESTAMPDIFF(SECOND,t.`begin_date`,t.`finish_date`) diff_sec 
FROM tache_info t LIMIT 0 , 10;

当前时间：

 SELECT TIMESTAMPDIFF(SECOND,t.`begin_date`, NOW()) diff_sec
 FROM tache_info t LIMIT 0 , 10;

处理：

 插入数据：

INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('1','1','0822-05-08 14:46:39','0822-05-08 14:46:39');
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('2','1','0822-05-08 14:46:42','0822-05-08 15:04:44');
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('3','22,33','0822-05-08 15:04:44',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('4','44,55','0822-05-08 17:02:00',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('5','22,33','0822-05-08 17:02:00',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('6','44,55','0822-05-08 17:18:39',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('7','66,77','0822-05-08 17:45:07',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('8','55,66','0822-05-08 17:45:07',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('9','55,66,77','0822-05-08 17:48:06',NULL);
INSERT INTO `tache_info` (`tch_id`, `person`, `created_date`, `finish_date`) VALUES('10','55,66,33','0822-05-08 17:48:06',NULL);

分离人员和计算滞留时间：

 SELECT t.`tch_id`, t.`created_date`, t.`finish_date`, 
TIMESTAMPDIFF(SECOND,t.`created_date`, NOW()) diff_sec,
	SUBSTRING_INDEX(
		SUBSTRING_INDEX(t.person,',',h_id + 1),',' ,- 1) deal_person
FROM
	help_topic, tache_info t
WHERE  h_id < (LENGTH(t.person)) - LENGTH(REPLACE (t.person, ',', '')) + 1 
AND finish_date IS NULL;

 

统计个人滞留环节总数、平均滞留时长（总滞留时长/总环节数）：

SELECT COUNT(DISTINCT tch_id), SUM(diff_sec)/COUNT(DISTINCT tch_id), deal_person FROM (
 SELECT t.`tch_id`, t.`created_date`, t.`finish_date`, TIMESTAMPDIFF(SECOND,t.`created_date`, NOW()) diff_sec,
	SUBSTRING_INDEX(
		SUBSTRING_INDEX(t.person,',',h_id + 1),',' ,- 1) deal_person
FROM
	help_topic, tache_info t
WHERE  h_id < (LENGTH(t.person)) - LENGTH(REPLACE (t.person, ',', '')) + 1 
AND finish_date IS NULL
) AS tch_group GROUP BY deal_person;

总结：

        mysql分离字符串，需要使用help_topic表作为辅助来处理。具体的字段，根据实际的来写就行。