[SQL] left join, inner join, group by, order by, count

Left join: 왼쪽에 붙이는 것. 해당되는 값이 없는 경우에는 Null로 표기됨

 

Inner join: 교집합이 나온다. 같이 공유하는 리스트를 중심으로 붙여짐.

select * from enrolleds e

inner join users u on e.user_id = u.user_id

---

Immerge by course_id, 

count couse_id and title,

and show the head of them.

select c1.course_id, c2.title, count(*) as cnt from checkins c1

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

group by c1.course_id

---

select pu.user_id , u.name, u.email, pu.point from point_users pu

inner join users u on pu.user_id = u.user_id

order by pu.point DESC

---

select name, count(*) as cnt from enrolleds e

inner join users u on e.user_id = u.user_id

where e.is_registered = 0

group by name

order by cnt DESC

 

남** 5
김** 4
이** 3
임** 2
신** 2
미** 1
곽** 1
강** 1
최** 1
테** 1
황** 1
류** 1
박** 1

---

select c.course_id , c.title , count(*) as cnt_notstart from courses c

inner join enrolleds e on c.course_id = e.course_id

where is_registered = 0

group by c.course_id

|course_id               |title  |cnt_notstart|
|------------------------|-------|------------|
|5f0ae408765dae0006002816|웹개발 종합반|14          |
|5f0ae408765dae0006002817|앱개발 종합반|10          |

---

select title, week, count(*) as cnt from courses c

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

group by title , week

order by title

|title  |week|cnt|
|-------|----|---|
|앱개발 종합반|1   |52 |
|앱개발 종합반|2   |18 |
|앱개발 종합반|3   |2  |
|웹개발 종합반|1   |44 |
|웹개발 종합반|2   |11 |
|웹개발 종합반|3   |7  |

---

select u.name, count(*) from users u

left join point_users pu on u.user_id = pu.user_id

where pu.point_user_id is not null

group by u.name

---

select count(pu.point_user_id) as pnt_user_cnt,

count(u.user_id) as tnt_user_cnt,

round(count(pu.point_user_id) / count(u.user_id),2) as ratio

from users u

left join point_users pu on u.user_id = pu.user_id

where u.created_at BETWEEN '2020-07-10'and '2020-07-20'

 

|pnt_user_cnt|tnt_user_cnt|ratio|
|------------|------------|-----|
|82          |202         |0.41 |

---

select e.enrolled_id,

e.user_id,

count(*) as cnt

from enrolleds_detail ed

inner join enrolleds e on ed.enrolled_id = e.enrolled_id

where ed.done = 1

group by e.enrolled_id, e.user_id

order by cnt desc