[Oracle/오라클] FIRST_VALUE() 필드의 첫번째 값, LAST_VALUE() 마지막 값 구하기
SELECT CLASS_CD, STUDENT_NO, STUDENT_NM, KOR FROM TB_GRADE; FIRST_VALUE(필드명) OVER (PARTITION BY 그룹명 ORDER BY 정렬필드명) 그룹중 최대값 구하기 SELECT CLASS_CD, STUDENT_NO, STUDENT_NM, KOR, FIRST_VALUE(KOR) OVER (PARTITION BY CLASS_CD ORDER BY KOR DESC) "CLASS_MAX" FROM TB_GRADE ORDER BY CLASS_CD, KOR DESC; MAX() 함수를 사용해도 동일한 결과 가능 SELECT CLASS_CD, STUDENT_NO, STUDENT_NM, KOR, MAX(KOR) OVER (PARTITION BY CLASS_..
2021. 10. 19.
[Oracle/오라클] 그룹함수 - COUNT, SUM, AVG, MAX, MIN, ROWS , + OVER, group by, having
MIN, MAX select min(ename), max(ename), min(hiredate), max(hiredate) from emp; 예제 테이블 SELECT * FROM TB_GRADE; SELECT CLASS_CD, MAX(KOR) KOR, MAX(ENG) ENG, MAX(MAT) MAT FROM TB_GRADE GROUP BY CLASS_CD; SELECT CLASS_CD, STUDENT_NO, STUDENT_NM, KOR, SUM(KOR) OVER(PARTITION BY CLASS_CD) AS KOR_SUM, MAX(KOR) OVER(PARTITION BY CLASS_CD) AS KOR_MAX FROM TB_GRADE ORDER BY CLASS_CD, STUDENT_NO; 1) GROUP ..
2021. 4. 17.
