Please enable JavaScript.
Coggle requires JavaScript to display documents.
오라클 공부, 오라클 Grouping() 과 Grouping_id() group by rollup, 날짜 포맷, Group…
-
-
날짜 포맷
-
-
1/4 분기 설정
SELECT TO_CHAR(HIREDATE,'Q'). HIREDATE FROM EMP;
-
Group by/rollup
-
rollup 3개를 준경우
select deptno,job,ename,sum(sal) from emp group by rollup(deptno,(job,ename))
select deptno,job,ename,sum(sal) from emp group by deptno,rollup((job,ename));
-
-
부서와,직업별 부서번호, 직무,연봉의 합계를 구해주세요
select deptno,job,sum(sal) from emp group by deptno,job order by deptno asc;
-
-
오라클 금요일 숙제~
함수
문자함수
-
instr
-
예문: select instr(ename,'a',1,1) from emp
substr
-
예문 : select substr(ename,1,2) from emp
replace
-
예문 :select replace ('100-001','0','*') from dual;
lpad
-
예문 : select lpad('abcd',10,'*') from dual
rpad
-
예문 : select rpad('abcd',10,'*') from dual
숫자함수
-
round
-
예문: select round(1.234,2) from dual
trunc
-
예문 : select trunc(12.3456,2) from dual
-
-
-
-
문자형식
substr(자를컬럼,보이고싶은것의 숫자,몇번째까지 보이고 싶은지)
집계함수
max
select max(nvl(sum(decode(부서번호,10,연봉)),0)) as 부서번호10,
max(nvl(sum(decode(부서번호,20,연봉)),0)) as 부서번호20,
max(nvl(sum(decode(부서번호,30,연봉)),0)) as 부서번호30
-
-
-
grouping_id(A,B) A와 B는 컬럼 명
-