경우에따라 (포인트를 구간별로 다르게 출력)
case when then lv
when then lv
else end
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 pu.user_id,
pu.point,
(case when point > 5000 then '잘 하고 있어요'
else '조그만 더 화이팅'end) as msg
from point_users pu
sub로 포인트 평균구하기
select avg(point) from point_users pu
복잡한 쿼리를 테이블을 사용 해서 간단하게
select * from checkins
where comment like '%화이팅%'
select a.enrolled_id,a.done_cnt, b.total_cnt from(
select enrolled_id, count()as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
)a
inner join (
select enrolled_id, count()as total_cnt from enrolleds_detail
group by enrolled_id
)b on a.enrolled_id = b.enrolled_id
with table1 as(
select enrolled_id, count()as 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
done은 0아니면 1 세는것이므로 sum 으로
select enrolled_id,
sum(done) as done_cnt,
count() as total_cnt,
round(sum(done)/count(),2) as ratio
from enrolleds_detail ed
group by enrolled_id
//select * from enrolleds_detail ed //
이제 강의를 다 들었는데 연습은 더 해야 하겠지요.
숙제제출하고 다시 정리
