MySQL去除重疊時(shí)間求時(shí)間差和的實(shí)現(xiàn)

SELECT TIMESTAMPDIFF(MINUTE, '2021-08-19 14:30:00', '2021-08-19 15:00:00') FROM DUAL;

CREATE TABLE test01 (
  id int(32) unsigned NOT NULL AUTO_INCREMENT,
  start_time datetime NOT NULL,
  end_time datetime NOT NULL,
  PRIMARY KEY (`id`)
) 
 
INSERT INTO test01(id, start_time, end_time) VALUES (1, '2021-08-18 16:27:51', '2021-08-18 17:27:59');
INSERT INTO test01(id, start_time, end_time) VALUES (2, '2021-08-18 17:20:26', '2021-08-18 20:10:37');
INSERT INTO test01(id, start_time, end_time) VALUES (3, '2021-08-18 22:05:57', '2021-08-18 23:55:20');

CREATE PROCEDURE sumTime()
BEGIN
    -- 定義變量 
 
    -- 是否首次
    DECLARE is_old int(1) DEFAULT 0;
 
    -- 上一次數(shù)據(jù)
	DECLARE old_start_time datetime;
	DECLARE old_end_time datetime;
 
	-- 本次數(shù)據(jù)
	DECLARE start_time datetime;
	DECLARE end_time datetime;
 
	-- 返回結(jié)果
	DECLARE num int(32) DEFAULT 0;
 
	-- 循環(huán)結(jié)束開關(guān)
	DECLARE done int DEFAULT 0;
 
	-- 創(chuàng)建游標(biāo)（查詢數(shù)據(jù)庫數(shù)據(jù)）
	DECLARE list CURSOR FOR SELECT a.start_time, a.end_time FROM test01 a;
 
    -- 定義最后一次循環(huán)時(shí)設(shè)置 循環(huán)結(jié)束開關(guān) 為 1
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
 
	-- 開啟游標(biāo)
	OPEN list;
 
		-- 開啟循環(huán)
		posLoop:LOOP
			-- 取值 將當(dāng)前循環(huán)的值取出 賦值給當(dāng)前數(shù)據(jù)變量
			FETCH list INTO start_time,end_time;
			-- 判斷是否首次
			if (is_old = 0) THEN 
 
				SET is_old = 1;
				SET old_start_time = start_time;
				SET old_end_time = end_time;
 
			-- 否則
			ELSE
				-- 校驗(yàn)是否在區(qū)間內(nèi)
				 if (start_time >= old_start_time AND start_time = old_end_time) THEN
 
					-- 校驗(yàn)結(jié)束時(shí)間是否不在在區(qū)間內(nèi)
				   if (end_time  old_start_time OR end_time > old_end_time) THEN
						SET old_end_time = end_time;
				   END IF;
 
				 -- 否則
				 ELSE
 
				   if (start_time  old_start_time )  THEN
 
						SET old_start_time = start_time;
 
					 ELSE
 
						SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
						SET old_start_time = start_time;
						SET old_end_time = end_time;
					 END IF;
				 END IF;
			END IF;
			-- 校驗(yàn)是否最后一次循環(huán)
			IF done=1 THEN 
			    SET num = num + TIMESTAMPDIFF(MINUTE, old_start_time, old_end_time);
			    LEAVE posLoop;
			END IF;
		-- 結(jié)束循環(huán)	
		END LOOP posLoop;
	-- 關(guān)閉游標(biāo) 
	CLOSE list;
	SELECT num;
END;

-- 調(diào)用存儲(chǔ)過程
call sumTime();

-- 刪除存儲(chǔ)過程
drop procedure if exists sumTime;