SQLize Online / PHPize Online  /  SQLtest Online

explain analyze with recursive dates(date) as ( select '2005-07-01' as date union all select date + interval 1 day from dates where date < '2005-07-31' ) , t0 as ( select store_id , date(rental_date) as rental_date , date(return_date) as return_date from inventory i join rental r on r.inventory_id = i.inventory_id where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01' or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01' ) , ren as ( select rental_date , count(case when store_id = 1 then 1 end) as c1 , count(case when store_id = 2 then 1 end) as c2 , count(*) as ct from t0 where rental_date >= date'2005-07-01'and rental_date < date'2005-08-01' group by rental_date ) , ret as ( select return_date , count(case when store_id = 1 then 1 end) as r1 , count(case when store_id = 2 then 1 end) as r2 , count(*) as rt from t0 where return_date >= date'2005-07-01'and return_date < date'2005-08-01' group by return_date ) select d.date , ifnull(c.c1,'0') as rented_at_store_1 , ifnull(r.r1,'0') as returned_at_store_1 , ifnull(c.c2,'0') as rented_at_store_2 , ifnull(r.r2,'0') as returned_at_store_2 , ifnull(c.ct,'0') as total_rented , ifnull(r.rt,'0') as total_returned from dates d left join ren c on c.rental_date = d.date left join ret r on r.return_date = d.date ; /* explain analyze select date_format(rental_date,'%Y-%c-%d') , count(*) from inventory i join rental r on r.inventory_id = i.inventory_id where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01' or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01' group by date_format(rental_date,'%Y-%c-%d') order by 1 ; select * from rental limit 5; select * from staff limit 5; select * from inventory limit 5; select store_id, count(*) from inventory group by store_id; */