SELECT rows where the numeric range specified by two fields (start-end) does not overlap with the range specified in other rows

I have the following table

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

      

I would like to fulfill a request

SELECT * FROM table

WHERE the range between start_time and end_time doesn't overlap with anything in the result set ordered by score DESC

So, in this case, the result set would be:

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

      

since the range between start_time

and end_time

for table.id =2

and table.id =3

overlaps with the range between start_time

and end_time

for table.id =4

, and score

of is table.id =4

greater than the estimate table.id =2

andtable.id =3

Can this be done strictly through mysql?

+3


source to share


1 answer


EDIT: Sorry, I had a small error. Now it really works.

Test data setup:

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)
;

      

Required function:



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 ;

      

How to use the function:

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

      

PS: Really good question. It was fun to decide

+3


source







All Articles