학생별 평균 점수를 계산한 후 그중 가장 높은 평균 점수만 조회하려면 서브쿼리(subquery)를 활용하면 됩니다. 이 글에서는 예제 테이블을 직접 만들고 단계별로 쿼리를 작성하는 방법을 살펴보겠습니다.
1단계: 테이블 생성
먼저 학생 이름과 점수를 저장할 테이블을 생성합니다.
mysql> create table DemoTable ( StudentName varchar(40), StudentMarks int ); Query OK, 0 rows affected (0.50 sec)
2단계: 데이터 삽입
insert 명령어를 사용해 샘플 데이터를 입력합니다.
mysql> insert into DemoTable values('Adam',56);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Chris',78);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Adam',89);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Robert',98);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('Chris',65);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('Robert',34);
Query OK, 1 row affected (0.20 sec)
3단계: 전체 데이터 확인
select 문으로 테이블에 저장된 모든 레코드를 조회합니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-------------+--------------+ | StudentName | StudentMarks | +-------------+--------------+ | Adam | 56 | | Chris | 78 | | Adam | 89 | | Robert | 98 | | Chris | 65 | | Robert | 34 | +-------------+--------------+ 6 rows in set (0.00 sec)
4단계: 최고 평균 점수 조회하기
다음 쿼리는 학생별 평균 점수를 구한 뒤, 그중 가장 높은 값만 반환합니다.
mysql> select max(avSal.StudentMarks) from (select avg(StudentMarks) as `StudentMarks` from DemoTable group by StudentName) as avSal;
실행 결과는 다음과 같습니다.
+-------------------------+ | max(avSal.StudentMarks) | +-------------------------+ | 72.5000 | +-------------------------+ 1 row in set (0.09 sec)
쿼리 동작 원리
이 쿼리가 어떻게 동작하는지 단계별로 살펴보겠습니다.
- 내부 서브쿼리:
group by StudentName으로 학생 이름별로 데이터를 묶은 후avg()함수로 각 학생의 평균 점수를 계산합니다. - 외부 쿼리: 서브쿼리가 반환한 평균 점수 목록에
max()함수를 적용해 가장 높은 평균 점수 하나만 추출합니다.
예제 데이터를 기준으로 각 학생의 평균 점수는 Adam 72.5점((56+89)/2), Chris 71.5점((78+65)/2), Robert 66점((98+34)/2)입니다. 따라서 최종 결과로 가장 높은 평균인 72.5000이 반환되는 것을 확인할 수 있습니다.