SQL Union, Subquery

Union(결과물 합치기)

 

이어 보고 싶을 때 이어주는거

 

 

 

 

 

 

 

 

ex)

(

select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2

inner join courses c on c2.course_id = c.course_id

inner join orders o on o.user_id = c2.user_id

where o.created_at < '2020-08-01'

group by c2.course_id, c2.week

)

union all

(

select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2

inner join courses c on c2.course_id = c.course_id

inner join orders o on o.user_id = c2.user_id

where o.created_at >= '2020-08-01'

group by c2.course_id, c2.week

)

 

**order by 는 있어도 정렬이 안됨 -> 이땐 SubQuery 사용하기

Subquery

ex)

SELECT u.user_id, u.name, u.email from users u

inner join orders o on u.user_id = o.user_id

where o.payment_method = 'kakaopay'

 

 -> SubQuery

(Where 에 들어가는 Subquery)

SELECT user_id, name, email from users u

where user_id in (

select user_id from orders o

where payment_method = 'kakaopay'

)

 

Select 에 들어가는 Subquery

ex)

SELECT c.checkin_id,

c.user_id,

c.likes,

(

SELECT avg(likes) from checkins

where user_id = c. user_id

) as avg_likes_user

from checkins c

 

From 에 들어가는 Subquery (가장 많이 사용되는 유형!)

 

ex)

select pu.user_id, pu.point, a.avg_likes from point_users pu

inner join (

select user_id, round(avg(likes),1) as avg_likes from checkins c

group by user_id

) a on pu.user_id = a.user_id