mysql-subquery사용하여 연산하기

조건문에 쿼리 결과를 이용하기 위해선 subquery를 사용하면 된다.

 

일단 customer와 food_order테이블을  조인후

customer_id별로 총금액을 과 총 주문 횟수를 구해줄것이다.

select 
c.name,
c.age,
sum(f.price) total,
count(f.price) count
from customers c inner join food_orders f
on c.customer_id=f.customer_id
group by c.customer_id

이 쿼리 결과를 이용하여

total금액과 count값을 기준으로 등급을 매기고,

age를 연령대로 나누려고한다.

 

등급을 나눠보자

total금액이 100000원이상이고,7번이상 주문을 한 경우 'vip'

total금액이 50000원이상이고,5번이상 주문을 한 경우 'gold'

total금액이 30000원이상이고,3번이상 주문을 한 경우 'silver'

조건에 충족하지 못하는 나머지의 경우 '일반'으로 매겨준다.

 

하지만 정렬시 vip,gold,silver로 정렬하기 위해서는 우선순위를 줘야한다.

그래서 숫자를 넣어준다.별칭은 우선순위라고 해준다.

case
when total>100000 and count>=7 then 1
when total>50000 and count>=5 then 2
when total>30000 and count>=3 then 3
else 4
end '우선순위'

 

이번에는 나이를 연령대로 구분하려고한다.

60세 이상은 '시니어'

50세이상은 '50대'

40세 이상은 '40대'

30세 이상은 '30대'

20세 이상은 '20대'

그 이하는 '유아,청소년'이라고 한다.

case 
when age>=60 then '시니어'
when age>=50 then '50대'
when age>=40 then '40대'
when age>=30 then '30대'
when age>=20 then '20대'
else '유아,청소년'
end '연령대'

 

등급을 매기기위해서 subquery를 사용할것이다.

sub query의 우선순위를 이용하여 vip,gold,silver로 나눌것이다.

case
when 우선순위=1 then 'vip'
when 우선순위=2 then 'gold'
when 우선순위=3 then 'silver'
else '일반'
end '등급'

이것을 쿼리문으로 완성을 해보자

select name,total,age,count,연령대,
case
when 우선순위=1 then 'vip'
when 우선순위=2 then 'gold'
when 우선순위=3 then 'silver'
else '일반'
end '등급'
from
(select name,total,age,count,
case 
when age>=60 then '시니어'
when age>=50 then '50대'
when age>=40 then '40대'
when age>=30 then '30대'
when age>=20 then '20대'
else '유아,청소년'
end '연령대',
case
when total>100000 and count>=7 then 1
when total>50000 and count>=5 then 2
when total>30000 and count>=3 then 3
else 4
end '우선순위'
from 
(
select 
c.name,
c.age,
sum(f.price) total,
count(f.price) count
from customers c inner join food_orders f
on c.customer_id=f.customer_id
group by c.customer_id) a) b
order by 우선순위

우선순위별 정렬