Page History

use jplist_node;

select p.gid,p.dtsday,p.dteday,p.region1,p.region2,p.name,
	   o.name as o_name ,
       p.price,
       min(o.price) as minprice, max(o.price) as maxprice,min(o.price)-p.price as diffprice, 
	   max(CASE WHEN o.sprovider='Agoda.com' THEN o.price ELSE NULL END ) AS 'Agoda.com',
       max(CASE WHEN o.sprovider='Amoma.com' THEN o.price ELSE NULL END ) AS 'Amoma.com',
       max(CASE WHEN o.sprovider='Daily' THEN o.price ELSE NULL END ) AS 'Daily',
       max(CASE WHEN o.sprovider='Expedia.co.kr' THEN o.price ELSE NULL END ) AS 'Expedia.co.kr',
       max(CASE WHEN o.sprovider='호텔에서 결제' THEN o.price ELSE NULL END ) AS '호텔에서 결제',
       max(CASE WHEN o.sprovider='호텔엔조이' THEN o.price ELSE NULL END ) AS '호텔엔조이',
       max(CASE WHEN o.sprovider='호텔조인' THEN o.price ELSE NULL END ) AS '호텔조인',
       max(CASE WHEN o.sprovider='호텔패스' THEN o.price ELSE NULL END ) AS '호텔패스',
       max(CASE WHEN o.sprovider='ExpediaHilton.co.krcom' THEN o.price ELSE NULL END ) AS 'Hilton.com',
       max(CASE WHEN o.sprovider='Expedia.co.kr'Hotels.com' THEN o.price ELSE NULL END ) AS 'Hotels.com',
       max(CASE WHEN o.sprovider='Trip.com' THEN o.price ELSE NULL END ) AS 'Trip.com',
       max(CASE WHEN o.sprovider='야놀자' THEN o.price ELSE NULL END ) AS '야놀자'
	from hotellog p 
	inner join mapping m on m.lid = p.gid
    inner join hotellog o on o.gid = m.gid and o.mprovider = m.mprovider and o.dtsday = p.dtsday and o.dteday = p.dteday
    where p.mprovider = 'Inter' and p.dtsday='20180115' and p.dteday='20180115'
    group by p.gid
    order by p.region1;