λ³Έλ¬Έ λ°”λ‘œκ°€κΈ°
Data Base/MySQL

MySQL 20λ²ˆμ§Έμˆ˜μ—…

by μ½”λ”©ν•˜λŠ” λΆ•μ–΄ 2021. 2. 9.
λ°˜μ‘ν˜•

β–Ά GROUP BY와 HAVING

​​

1. μ—΄μ˜ 그룹화​

​

예제 11-1) STUDENT ν…Œμ΄λΈ”μ— μžˆλŠ” ν•™μƒμ˜ μž…ν•™λ…„λ„λ³„ 그룹을 좜λ ₯ν•˜λΌ.

A)

mysql> select substring(stu_no, 1, 4)

-> from student

-> group by substring(stu_no, 1, 4);

​

​

예제 11-2) 각 μž…ν•™λ…„λ„λ³„ 총 학생 수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select substring(stu_no, 1, 4), count(*)

-> from student

-> group by substring(stu_no, 1, 4);

​

​

예제 11-3) λ“±λ‘ν•œ 학생에 λŒ€ν•˜μ—¬ ν•™λ²ˆ, λ“±λ‘νšŸμˆ˜, 각 학생이 받은 μž₯ν•™κΈˆμ˜ 전체 합을 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, count(*), sum(jang_total)

-> from fee

-> group by stu_no;

​

​

예제 11-4) '박정인' 학생에 λŒ€ν•˜μ—¬ ν•™λ²ˆ, λ“±λ‘ν•œ 횟수의 수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, count(*)

-> from fee

-> where stu_no in

-> (select stu_no from student where stu_name = '박정인')

-> group by stu_no;

​

​

​

2. 2개 μ΄μƒμ˜ 열에 λŒ€ν•œ κ·Έλ£Ήν™”

​

​

예제 11-5) 학년별 μ£Όμ•Ό 인원을 좜λ ₯ν•˜λΌ. (단, 좜λ ₯ μˆœμ„œλŠ” 학년별 μ˜€λ¦„μ°¨μˆœ, μ£Όμ•Ό μ˜€λ¦„μ°¨μˆœμ΄λ‹€)

A)

mysql> select grade, juya, count(*)

-> from student

-> group by grade, juya

-> order by grade, juya;

​

​

예제 11-6) STUDENT ν…Œμ΄λΈ”μ—μ„œ ν•™λ…„, 반, 주야ꡬ뢄이 μ„œλ‘œ λ‹€λ₯Έ λͺ¨λ“  쑰합을 μΈμ›μˆ˜λ‘œ 좜λ ₯ν•˜λΌ.

A)

mysql> select grade, class, juya, count(*)

-> from student

-> group by grade, class, juya;

​

​

예제 11-7) FEE ν…Œμ΄λΈ”μ—μ„œ 각 ν•™μƒλ³„λ‘œ λŒ€ν•™ μž¬ν•™μ‹œ 총 λ‚©μž…ν•œ κΈˆμ•‘κ³Ό λ“±λ‘κΈˆ μ΅œλŒ€κ°’, κ°€μž₯ 적게 받은 μž₯ν•™κΈˆ, 등둝 횟수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, sum(fee_pay), max(fee_total),

-> min(jang_total), count(*)

-> from fee

-> group by stu_no;

​

​

예제 11-8) λ“±λ‘ν•œ 학생에 λŒ€ν•˜μ—¬ ν•™λ²ˆ, 이름, λ‚©μž…κΈˆμ˜ 총앑을 좜λ ₯ν•˜λΌ.

A)

mysql> select s.stu_no, stu_name, sum(fee_pay)

-> from student s, fee f

-> where s.stu_no = f.stu_no

-> group by s.stu_no;

​

​

​

3. μˆ˜μ‹μ˜ κ·Έλ£Ήν™”

​

​

예제 11-9) 등둝 연도에 λŒ€ν•˜μ—¬ λ“±λ‘λœ 수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select fee_year, count(*)

-> from fee

-> group by fee_year;

​

​

예제 11-10) 동아리 κ°€μž…λ²ˆν˜Έλ₯Ό 기초둜 ν•˜μ—¬ 학생듀을 κ·Έλ£Ήν™”ν•˜λΌ. 이 λ•Œ κ·Έλ£Ή 1은 κ°€μž…λ²ˆν˜Έ 1λΆ€ν„° 3κΉŒμ§€μ΄λ©°, κ·Έλ£Ή 2λŠ” κ°€μž…λ²ˆν˜Έ 4λΆ€ν„° 6κΉŒμ§€μ˜ μˆœμ„œλ‘œ 3λͺ…씩을 ν•œ 그룹으둜 κ·Έλ£Ήν™”ν•œλ‹€. 그리고 각 그룹에 λŒ€ν•˜μ—¬ ν•™μƒμ˜ μˆ˜μ™€ κ°€μž₯ 높은 ν•™λ²ˆμ„ 좜λ ₯ν•œλ‹€.

A)

mysql> select ceil(cir_num/3), count(*), max(stu_no)

-> from circle

-> group by ceil(cir_num/3);

​

​

​

4. NULLκ°’μ˜ κ·Έλ£Ήν™”

​

​

예제 11-11) μ„œλ‘œ λ‹€λ₯Έ μž₯ν•™μ½”λ“œλ₯Ό κ·Έλ£Ήν™”ν•˜κ³  μΈμ›μˆ˜λ₯Ό 좜λ ₯ν•˜λΌ.​

A)

mysql> select ifnull(jang_code, null) "μž₯ν•™μ½”λ“œ", count(*) ← ifnull μ•ˆ 써도 됨.

-> from fee

-> group by jang_code;

​

​

​

5. HAVING 절

​

​

예제 11-12) μ„Έ 번 이상 λ“±λ‘ν•œ ν•™μƒμ˜ ν•™λ²ˆκ³Ό 등둝 횟수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, count(*)

-> from fee

-> group by stu_no

-> having count(*) > 2;

​

​

예제 11-13) 2019년에 λ“±λ‘ν•œ ν•™μƒμ˜ ν•™λ²ˆκ³Ό 등둝 횟수λ₯Ό 좜λ ₯ν•˜λΌ.​

A)

mysql> select stu_no, fee_year, count(*)

-> from fee

-> group by stu_no, fee_year

-> having fee_year = '2019';

​

​​

예제 11-14) μž¬ν•™μ€‘μ— λ‚©λΆ€ν•œ λ“±λ‘κΈˆμ˜ 전체 λ‚©λΆ€κΈˆμ•‘μ΄ 5,000,000원 이상인 각 학생에 λŒ€ν•˜μ—¬ 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> group by stu_no

-> having sum(fee_pay) >= 5000000;

​

​

예제 11-15) λ‚¨ν•™μƒμ΄λ©΄μ„œ μž¬ν•™μ€‘ λ‚©λΆ€ν•œ μ „μ²΄λ“±λ‘κΈˆμ΄ 2,000,000원 이상인 ν•™μƒμ˜ ν•™λ²ˆκ³Ό λ“±λ‘κΈˆμ˜ 총앑을 좜λ ₯ν•˜λΌ.​

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> where stu_no in

-> (select stu_no from student where gender in (1, 3, 5))

-> group by stu_no

-> having sum(fee_pay) >= 2000000;

​

​

예제 11-16) μž¬ν•™μ€‘ λ‚©λΆ€ν•œ λ“±λ‘κΈˆ 총앑이 κ°€μž₯ λ§Žμ€ 각 학생에 λŒ€ν•œ ν•™λ²ˆκ³Ό λ“±λ‘κΈˆμ˜ 총앑을 좜λ ₯ν•˜λΌ. (λ§Œμ•½ λ“±λ‘κΈˆ 총앑이 λͺ¨λ‘ κ°™κ±°λ‚˜ λͺ¨λ‘ λ§Žλ‹€λ©΄, 이 μ§ˆμ˜μ–΄λŠ” λ§Žμ€ 학생을 λ°˜ν™˜ν•  것이닀)

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> group by stu_no

-> having sum(fee_pay) >= all

-> (select sum(fee_pay) from fee group by stu_no);

​

β˜…GROUP BYλ₯Ό μ‚¬μš©ν•˜λ©΄ DISTINCTλ₯Ό μ“Έ ν•„μš”κ°€ μ—†λ‹€.

​

​

<μ—°μŠ΅λ¬Έμ œ>

11-2) STUDENT ν…Œμ΄λΈ”λ‘œλΆ€ν„° μ„œλ‘œ λ‹€λ₯Έ μΆœμƒ 연도와 인원을 좜λ ₯ν•˜λΌ.​

A)

mysql> select substring(birthday, 1, 4), count(*)

-> from student

-> group by substring(birthday, 1, 4);

​

​

11-3) 적어도 ν•œ 번 이상 μž₯ν•™κΈˆμ„ 지급받은 ν•™μƒμ˜ ν•™λ²ˆκ³Ό μž₯ν•™κΈˆμ΄μ•‘, μž₯ν•™κΈˆ 평균값, μž₯ν•™κΈˆ 지급 받은 횟수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, sum(ifnull(jang_total, 0)), avg(ifnull(jang_total, 0)), count(*)

-> from fee

-> group by stu_no

-> having count(*) >= 1;

​

mysql> select stu_no, sum(jang_total), avg(jang_total), count(jang_total)

-> from fee

-> group by stu_no

-> having count(jang_total) >= 1;

​

​

11-4) ꡐ과λͺ© ν…Œμ΄λΈ”μ—μ„œ κ³Όλͺ©μ΄ μƒμ„±λœ μ—°λ„λ‘œ κ·Έλ£Ήν™”ν•˜μ—¬ κ³Όλͺ© 수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select create_year, count(sub_code)

-> from subject

-> group by create_year;

​

​

11-5) ꡐ수 ν…Œμ΄λΈ”μ—μ„œ κ΅μˆ˜κ°€ λŒ€ν•™μ— λ°œλ Ήλ°›μ€ μ—°λ„λ‘œ κ·Έλ£Ήν™”ν•˜μ—¬ ꡐ수 인원을 좜λ ₯ν•˜λΌ.

A)

mysql> select year(create_date), count(create_date)

-> from professor

-> group by year(create_date)

-> order by year(create_date);

​

​

11-6) 경기도 지역에 κ±°μ£Όν•˜λŠ” ν•™μƒμ˜ ν•™λ²ˆκ³Ό 이름, 우편번호, μ£Όμ†Œλ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, stu_name, post_no, address

-> from student

-> where substring(address, 1, 3) = '경기도';

​

​

11-7) λ‚©λΆ€ν•œ λ“±λ‘κΈˆ 총앑이 5,000,000원 미만인 ν•™μƒμ˜ ν•™λ²ˆκ³Ό λ“±λ‘κΈˆ 총앑을 좜λ ₯ν•˜λΌ.

A)

mysql> select stu_no, sum(fee_pay)

-> from fee

-> group by stu_no

-> having sum(fee_pay) < 5000000;

​

​

11-8) μˆ˜κ°•μ‹ μ²­ κ³Όλͺ©μ΄ 2κ³Όλͺ© 이상인 ν•™μƒμ˜ ν•™λ²ˆ, 이름, μˆ˜κ°•μ‹ μ²­ κ³Όλͺ© 수λ₯Ό 좜λ ₯ν•˜λΌ.

A)

mysql> select a.stu_no, stu_name, count(sub_code)

-> from student s, attend a

-> where s.stu_no = a.stu_no

-> group by a.stu_no

-> having count(sub_code) >= 2;

​

​

11-9) μž₯ν•™κΈˆμ„ κ°€μž₯ 많이 받은 ν•™μƒμ˜ ν•™λ²ˆ, 이름, μž₯ν•™κΈˆ 총앑을 좜λ ₯ν•˜λΌ.​

A)

mysql> select f.stu_no, stu_name, sum(jang_total)

-> from student s, fee f

-> where s.stu_no = f.stu_no

-> group by f.stu_no

-> having sum(jang_total) >= all

-> (select sum(jang_total) from fee group by stu_no);

λ°˜μ‘ν˜•

'Data Base > MySQL' μΉ΄ν…Œκ³ λ¦¬μ˜ λ‹€λ₯Έ κΈ€

MySQL 22λ²ˆμ§Έμˆ˜μ—…  (0) 2021.02.09
MySQL 21λ²ˆμ§Έμˆ˜μ—…  (0) 2021.02.09
MySQL 19λ²ˆμ§Έμˆ˜μ—…  (0) 2021.02.08
MySQL 18λ²ˆμ§Έμˆ˜μ—…  (0) 2021.02.08
MySQL 17λ²ˆμ§Έμˆ˜μ—…  (0) 2021.02.08

λŒ“κΈ€