mysql - 选择由两个字段(开始 - 结束)指定的数字范围不与任何其他行中指定的范围重叠的行

Question

我有下表

id  score   start_time  end_time
1   60      25          30
2   85      5           10
3   90      10          15
4   100     0           20

我要执行查询

SELECT * FROM 表

WHERE start_time 和 end_time 之间的范围不与按分数 DESC 排序的结果集中的任何内容重叠

所以在这种情况下，结果集将是:

id  score   start_time  end_time
4   100     0           20
1   60      25          30

由于 start_time 和 end_time 之间的范围 table.id =2 和 table.id =3与 table.id =4 和 table 的 score 的 start_time 和 end_time 之间的范围重叠.id =4 大于 table.id =2 和 table.id =3

的分数

是否可以通过mysql严格做到这一点？

Answer 1

编辑:抱歉，里面有一个小错误。现在真的有用了。

设置测试数据:

create table test(
id int,
score int,
start_time int,
end_time int
);

insert into test values
(5, 95, 0, 15), /*extra test case from me*/
(1,   60,      25,          30),
(2,   85,      5,           10),
(3,   90,      10,          15),
(4,   100,     0,           20)
;

需要的功能:

DELIMITER $$
DROP FUNCTION IF EXISTS checkOverlap$$
CREATE FUNCTION checkOverlap(p_id INT, p_score INT, p_stime INT, p_etime INT)
RETURNS BOOL
READS SQL DATA 
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE result INT DEFAULT TRUE;

DECLARE stime, etime INT;

DECLARE cur1 CURSOR FOR SELECT start_time, end_time FROM test WHERE id != p_id AND score > p_score;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP
FETCH cur1 INTO stime, etime;
IF done THEN
LEAVE read_loop;
END IF;

IF ((p_stime >= stime AND p_stime <= etime) OR (p_etime >= stime AND p_etime <= etime)) THEN
SET result = FALSE;
END IF;
END LOOP;

CLOSE cur1;

RETURN result;

END$$

DELIMITER ;

函数使用方法:

select *
from
test
where
checkOverlap(id, score, start_time, end_time) = TRUE
order by score desc

P.S:非常好的问题。解决起来很有趣