當前位置:網站首頁>mysql split 字符串

mysql split 字符串

2022-05-15 05:40:16天狼1222

使用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錶作為輔助來處理。具體的字段,根據實際的來寫就行。

版權聲明
本文為[天狼1222]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/135/202205142240235641.html

隨機推薦