βΆ 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 |
λκΈ