select * from point_users pu
where point > (
SELECT AVG(point) from point_users pu
where user_id in (
select user_id from users
where name = '이**'
)
)<select 절에 들어가는 서브쿼리 퀴즈>
1. 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- checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙여보기
select c.checkin_id,
c2.title,
c.user_id,
c.likes,
(
select ROUND(AVG(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
inner join courses c2 on c.course_id = c2.course_id<from 절에 들어가는 서브쿼리 퀴즈>
1. course_id 별 유저의 체크인 개수를 구해보기
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from
(
select course_id, count(DISTINCT(user_id)) as cnt_checkins from checkins
group by course_id
) a
inner join (
select course_id, count(*) as cnt_total from orders
group by course_id
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
<문자열>
1. 문자열 쪼개보기 : SUBSTRING_INDEX(필드명, 기준문자, )
=> -1: 두번째, 1: 첫번째 select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
2. 문자열 필요한 부분만 추출 : SUBSTRING(필드명, 시작점, 시작점부터 끝)select order_no, SUBSTRING(created_at, 1, 10) as date from orders o
-> 날짜별로 몇 개의 주문이 있는지 카운트select SUBSTRING(created_at, 1, 10) as date, count(*) from orders o
group by date
<Case> : 경우에 따라 원하는 값을 새 필드에 출력
-> 특정 조건에 따라 데이터를 구분해서 정리해주고 싶을 때
1. 10000점보다 높은 포인트를 가지고 있으면 'good', 보다 낮으면 'sad' 라고 표시select pu.user_id, pu.point,
(case when pu.point > 10000 then 'good'
else 'sad' end) as msg
from point_users pu
with table1 as (
select pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
)
select a.lv, count(*) as cnt from table1 a
group by a.lv
퀴즈 1) 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요'
낮으면 '열심히 합시다!' 표시하기with table1 as (
select AVG(point) as avg from point_users
)
select pu.point_user_id,
pu.point,
(CASE when pu.point >= t.avg then '잘 하고 있어요!'
else '열심히 합시다!' end) as msg
from point_users pu, table1 t
퀴즈 2) 이메일 도메인별 유저의 수 세어보기select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
퀴즈 3) comment에 '화이팅'이 들어간 것만select * from checkins
where comment like '%화이팅%'
퀴즈4) 수강등록정보 별 전체 강의 수와 들은 강의 수 출력with table1 as (
select enrolled_id, COUNT() done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count() as total_cnt from enrolleds_detail
group by enrolled_id
)
select a.enrolled_id,
a.done_cnt,
b.total_cnt,
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id
퀴즈5) 수강 등록정보 별 전체 강의 수와 들은 강의 수, 그리고 진도율with table1 as (
select enrolled_id, COUNT() done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count() as total_cnt from enrolleds_detail
group by enrolled_id
)
select a.enrolled_id,
a.done_cnt,
b.total_cnt,
ROUND((a.done_cnt/b.total_cnt),2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id
같은 코드1!!!!!
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail
group by enrolled_id
