๋ทฐ (view)
๐ก ํ๋ ์ด์์ ๊ธฐ๋ณธ ํ ์ด๋ธ์ด๋ ๋ค๋ฅธ ๋ทฐ๋ฅผ ์ด์ฉํ์ฌ ์์ฑ๋๋ ๊ฐ์ ํ ์ด๋ธ์์์ ๋ณ, ์๊ฐ๋ณ ์ฃผ๋ฌธ๊ฑด์ ์ง๊ณํ๊ธฐ
select a.restaurant_name,
substring(b.time, 1, 2) hh,
count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
Pivot view ๊ตฌ์กฐ ๋ง๋ค๊ธฐ
select restaurant_name,
max(if(hh='15', cnt_order, 0)) "15",
max(if(hh='16', cnt_order, 0)) "16",
max(if(hh='17', cnt_order, 0)) "17",
max(if(hh='18', cnt_order, 0)) "18",
max(if(hh='19', cnt_order, 0)) "19",
max(if(hh='20', cnt_order, 0)) "20"
from
(
select a.restaurant_name,
substring(b.time, 1, 2) hh,
count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc