[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.
[Oracle] 데이터 타입 변환 함수 - 날짜 / 숫자
SYSDATE FORMAT 변경 ALTER SESSION SET nls_date_format ='yyyy-mm-dd'; ALTER SESSION SET nls_date_format ='yyyy-mm-dd hh24:mi:ss'; DY, DAY 요일구하기 select sysdate, TO_CHAR(sysdate,'DY'), TO_CHAR(sysdate,'DAY') from dual; SELECT sysdate, to_char(sysdate, 'Q') 분기, to_char(sysdate, 'DDD') "올해 몇 번째 날", to_char(sysdate, 'DY') "요일" , to_char(sysdate, 'D') "요일을 숫자로", to_char(sysdate, 'WW') "올해 몇 번째 주" FROM du..
2021. 4. 13.
[Oracle] 날짜 함수 - ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, TRUNC, EXTRACT, INTERVAL
select sysdate, sysdate+300, add_months(sysdate, 10), sysdate +365*10, add_months(sysdate, 12*10) from dual; 기간 개월 수 구하기 MONTHS_BETWEEN(최근, 과거) select ename, hiredate, trunc(months_between(sysdate, hiredate)) 근속월, trunc(months_between(sysdate, hiredate)/12) 근속년 from emp; 해당월 마지막일자 구하기 LAST_DAY select last_day(sysdate) from dual; 부분 초기화 TRUNC select sysdate, trunc(sysdate), trunc(sysdate,'yy'), t..
2021. 4. 12.
[Oracle] 문자/숫자 함수
LPAD(선택칼럼, 문자수, 치환문자) , RPAD LTRIM(선택칼럼, 삭제하고자 하는 문자) , RTRIM - 첫 문자만 삭제, TRIM - 양쪽 문자 삭제 select ename, LTRIM(ename, 'S'), RTRIM(ename, 'S'), TRIM('T' from ename)from emp; REPLACE / TRANSLATE select ename, replace(ename, 'CO', '**'), TRANSLATE(ename, 'CO', '*5') from emp; 숫자 함수(ROUND, TRUNC, MOD, ABS, FLOOR, CEIL, SIGN, POWER(M, N)
2021. 4. 11.
