2023/1/16

서브쿼리

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';

ㄴ>이렇게도 할 수 있다.

select user_id, name, email from users
where user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
);

select user_id from orders
where payment_method = 'kakaopay';

---

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;

select avg(likes) from checkins
where user_id = '1'

from 절에서의 subquery

원래 있는 테이블 인 것 처럼 사용하는 것.

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
						group by user_id
						) a on pu.user_id = a.user_id

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

---

• 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출

select * from point_users
where point > (select avg(point) from users);

select avg(point) from point_users;

• 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 유저들의 데이터 구하기

select * from point_users
where point > (
	select avg(pu.point) from point_users pu
	inner join users u
	on pu.user_id = u.user_id
	where u.name = '이**' 
);

# 혹은

select * from point_users
where point > (
	select avg(point) from point_users
	where user_id in (
		select user_id from users where name = '이**'
	)
);

select avg(pu.point) from point_users pu
inner join users u
on pu.user_id = u.user_id
where u.name = '이**'; 

• checkins 테이블에 course_id 별 평균 likes 수 필드 우측에 붙여보기

select c.checkin_id,
			 c.course_id,
			 c.user_id,
			 c.likes,
				(
					select avg(likes) from checkins 
					where course_id = c.course_id
				) as course_avg
from checkins c