subquery & groupfunction

이전에 join을 했던 것 을 간단하게 사용할 수 있는 것이 subquery이다.

많이 설명하는 것보다 간단하게 보이면

 

student table에서 이름이 '유승범'인 학생과 같은 조원 출력하기

SELECT * FROM student WHERE teamno = (SELECT teamno FROM student WHERE name = '유승범');

 

혹은 조 이름이 '모꼬지' 조의 조원 studentno, name, teamno 출력하기

SELECT studentno, name, teamno FROM student WHERE teamno = (SELECT teamno FROM team WHERE teamname = '모꼬지');

 

 

group function : count(*), sum(Age), avg(age), max(age), min(age) 같은 것들이 존재한다.

group function에서 조건을 줘야만 할 때는 having~ 을 활용한다.

또한 다른 column을 추가할 때는 group by를 활용한다.

 

이를 활용해 나이가 20이상인 학생을 뽑아 계산해보면

SELECT count(*), sum(Age), avg(age), max(age), min(age)
FROM student WHERE age >=20;

 

20살 이상 중 팀에 따라 계산을 해보면

SELECT teamno, count(*), sum(Age), avg(age), max(age), min(age)
FROM student WHERE age >=20 GROUP BY teamno ;

이렇게 볼 수 있다.

 

또한 having을 사용해서 특정한 것만 조건을 설정할 수 있다.

SELECT teamno, count(*), sum(Age), avg(age), max(age), min(age)
FROM student GROUP BY teamno HAVING count(*) >=4;

 

간접적으로 avg(age)를 where에 적용할 수도 있다.

아래는 팀별로 나이에 대한 것을 통계낸 것이다.

SELECT s.teamno, count(*), sum(Age), avg(age), max(age), min(age), t.teamname
FROM student s JOIN team t ON s.teamno = t.teamno 
GROUP BY teamno, teamname;

 

만약에 사람의 이름과 그 번호까지 알기 위해서는 많은 join을 활용하거나 subquery를 활용해야한다.

아래의 두가지의 코드는 같은 결과를 보인다.

SELECT s.teamno, count(*), sum(s.Age), avg(s.age), max(s.age), min(s.age), t.teamname, s1.name, s1.studentno
FROM student s JOIN team t ON s.teamno = t.teamno
JOIN student s1 ON  s1.teamno = s.teamno AND 
s1.age = (SELECT max(s2.age) FROM student s2 WHERE s2.teamno = s.teamno)
GROUP BY s.teamno, t.teamname, s1.name, s1.studentno;

SELECT s.teamno, count(*), sum(Age), avg(age), max(age), min(age), t.teamname, 
(SELECT s1.name FROM student s1 WHERE s1.age = max(s.age) AND s1.teamno = s.teamno) oldestMan, 
(SELECT s1.studentno FROM student s1 WHERE s1.age = max(s.age) AND s1.teamno = s.teamno) oldestManNumber
FROM student s JOIN team t ON s.teamno = t.teamno GROUP BY teamno, teamname;

이처럼 join을 활용할 수도 subquery를 활용할 수도 있다.