使用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表作为辅助来处理。具体的字段,根据实际的来写就行。