In this blog, we will write some MySQL queries to find the missing value in the given interval.
Here list of integer values are given values:
1, 2, 5, 6, 7, 10, 11, 12
Query to create the table and insert values into this tables:
Query 1 :
mysql> create table input(
id int not null primary key
);
insert into input(id) values (1),(2),(5),(6), (10), (11), (12);
Solution:
It displays missing value with a range between the last value.
mysql> select start, end from (
select m.id + 1 as start,
(select min(id) - 1 from input as x where x.id > m.id) as end
from input as m
left outer join input as r on m.id = r.id - 1
where r.id is null
) as x
where end is not null;
Output:
3 4
7 9
It displays missing value with the range including last set(any default)value(16).
Solution:
select x.start, ( case when (x.end is null) then 16 else x.end end) as end from (
select m.id + 1 as start,
(select min(id) - 1 from input as x where x.id > m.id) as end
from input as m
left outer join input as r on m.id = r.id - 1
where r.id is null
) as x;
Output
3 4
7 9
13 16
Query 2 :
Overlapping order in the given time periods
In this query, we will find the:
mysql> create table order(id integer, item varchar(100), starttime time, endtime time); insert into order(id, item, starttime, endtime) values(1, "Cake", 083000, 113000); insert into order(id, item, starttime, endtime) values(2, "Chicken", 110000, 123000); insert into order(id, item, starttime, endtime) values(3, "Chicken", 114000, 010000); insert into (id, item, starttime, endtime) values(4, "Steak", 080000, 100000); select * from order;
Solution:
mysql> update order set endtime = TIME_FORMAT('12:59:00', '%H:%i:%s') where id = 3;
select t1.id,(select count(*) from order as t2 where ( (t2.cooktime >= t1.order) and (t2.starttime <= t1.endtime) and (t2.id <> t1.id) ) or ( (t2.endtime >= t1.starttime) and (t2.endtime <= t1.endtime) and (t2.id <> t1.id) ) ) as maxcount from order as t1 ;