[MySQL] MySQL 서브쿼리

SELECT select_list
FROM TABLE 또는 View
WHERE 조건 연산자 ( SELECT select_list
                                        FROM TABLE
                                      WHERE 조건);

---

Anthony Hopkins 학생과 동일한 DEPTNO1의 이름과 학과 출력(서브쿼리)

SELECT S.NAME AS "STUD_NAME"
	 , D.DNAME AS "DEPT_NAME"
  FROM STUDENT S, department D
WHERE S.DEPTNO1 = D.DEPTNO -- 20
-- Anthony Hopkins 학생과 동일한 DEPTNO1의 학과
  AND S.DEPTNO1= 103;

103 = (SELECT DEPTNO1
		 FROM STUDENT
		WHERE NAME = "Anthony Hopkins")

---

도메인 그룹별 갯수와 전체 도메인중 차지하는 비율

SELECT SUBSTR(EMAIL,INSTR(EMAIL,'@')+1) AS "DOMAIN"
, COUNT(*) AS "EA"
, SUM(COUNT(*)) OVER() AS "SUM_DOMAIN"
, (COUNT(*) / SUM(COUNT(*)) OVER()) * 100 AS "%"
FROM PROFESSOR
GROUP BY SUBSTR(EMAIL,INSTR(EMAIL,'@')+1)
ORDER BY SUBSTR(EMAIL,INSTR(EMAIL,'@')+1);

---

전공이 201인 학과의 평균몸무게 보다 많은 학생들의 이름(서브쿼리)

SELECT NAME
	 , WEIGHT
  FROM STUDENT
  WHERE WEIGHT > (SELECT AVG(WEIGHT)
				    FROM STUDENT
				   WHERE DEPTNO1 = 201);

---

(다중행 서브쿼리)

-- 다중행 서브쿼리
SELECT EMPNO
	 , NAME
     , DEPTNO
  FROM EMP2
  WHERE DEPTNO IN(SELECT DCODE
				  FROM DEPT2
				  WHERE AREA = 'Pohang Main Office');

---

서브쿼리 조건2개

SELECT GRADE
	 , NAME
     , WEIGHT
 FROM STUDENT
 WHERE (GRADE,WEIGHT) IN(SELECT GRADE
														  , MAX(WEIGHT)
												   FROM STUDENT
											 GROUP BY GRADE)
ORDER BY GRADE;