그룹별로 데이터 출력하기(groupby, PARTITION BY)

SELECT DEPTNO, SAL FROM emp;

SELECT DEPTNO, SUM(SAL) FROM emp GROUP BY DEPTNO;

---

SELECT deptno, sal, ROWNUM
from(
SELECT DEPTNO,sal FROM emp ORDER BY deptno, sal DESC)
WHERE DEPTNO = 10
UNION ALL
SELECT deptno, sal, ROWNUM
from(
SELECT DEPTNO,sal FROM emp ORDER BY deptno, sal DESC)
WHERE deptno = 20
UNION ALL
SELECT deptno, sal, ROWNUM
from(
SELECT DEPTNO,sal FROM emp ORDER BY deptno, sal DESC)
WHERE deptno = 30;

---

년도별 학생들의 키 순위

select name,birthday,height, ROWNUM FROM(
select * from student)
where SUBSTR(birthday,0,4) = 1975
UNION ALL
select name,birthday,height, ROWNUM FROM(
select * from student)
where SUBSTR(birthday,0,4) = 1976
UNION ALL
select name,birthday,height, ROWNUM FROM(
select * from student)
where SUBSTR(birthday,0,4) = 1977
UNION ALL
select name, birthday,height, ROWNUM FROM(
select * from student)
where SUBSTR(birthday,0,4) = 1978;

---

PARTITION BY

select * from emp;
select deptno,sal, sum(sal) OVER (PARTITION BY deptno ORDER BY sal_order DESC)
from(select deptno, sal,ROW_NUMBER() OVER (ORDER BY sal) sal_order
from emp);