오라클 쿼리문으로 달력출력하기
문제1)달력만들기를 해봅시다!! SQL>select level cnt,trunc(sysdate,'mm') sday from dual connect by levelSELECT decode(to_char(sday+cnt-1,'d'),1,cnt) 일 FROM(select level cnt,trunc(sysdate,'mm') sday from dual connect by levelSELECT max(decode(to_char(sday+cnt-1,'d'),1,A.cnt)) 일, max(decode(to_char(sday+cnt-1,'d'),2,A.cnt)) 월, max(decode(to_char(sday+cnt-1,'d'),3,A.cnt)) 화, max(decode(to_char(sday+cnt-1,'d'),4,..
최대값과 최소값 제외한 평균,총합 구하기(GREATEST,LEAST)
WITH TE AS ( SELECT '9' A, '7' B, '1' C, '5' D, '4' E, '6' F, '7' G, '4' H FROM DUAL UNION ALL SELECT '1' A, '7' B, '8' C, '7' D, '4' E, '3' F, '9' G, '2' H FROM DUAL UNION ALL SELECT '5' A, '9' B, '5' C, '6' D, '6' E, '6' F, '5' G, '5' H FROM DUAL UNION ALL SELECT '9' A, '7' B, '4' C, '2' D, '2' E, '7' F, '3' G, '3' H FROM DUAL UNION ALL SELECT '1' A, '8' B, '3' C, '6' D, '9' E, '5' F, '7' G, '..
컬럼 가로세로 바꾸기
with my_table as( SELECT MAX(GUBUN) GUBUN, MAX(M) M, MAX(M2) M2, MAX(DIV) DIV, MAX(PER) PER FROM ( SELECT '멋쟁이' GUBUN, '' M, '' M2, '' DIV, '' PER FROM DUAL UNION ALL SELECT '' GUBUN, '3630' M, '' M2, '' DIV, '' PER FROM DUAL UNION ALL SELECT '' GUBUN, '' M, '2847' M2, '' DIV, '' PER FROM DUAL UNION ALL SELECT '' GUBUN, '' M, '' M2, '783' DIV, '' PER FROM DUAL UNION ALL SELECT '' GUBUN, '' M, ''..
동일 컬럼값 안보여주기
1.동일 칼럼값 안보여주기 [1번문제] 원래 테이블:test11 먼저 학년별로 가공하기 SELECT COLL ,DEPT,DECODE(CNT,1,'1학년',2,'2학년',3,'3학년',4,'4학년') K, DECODE(CNT,1,FRE,2,SUP,3,JUN,4,SEN) A FROM TEST11,(SELECT ROWNUM CNT FROM USER_TABLES WHERE ROWNUM
학년별로 가공후 중복된 칼럼 이름 안보이게 하기(3가지 풀이법)
--구 select decode(A.COLL,B.COLL,' ',A.COLL) line, decode(A.COLL||A.DEPT, B.COLL||B.DEPT,' ',A.DEPT)SPEC, A.K,A.A FROM (SELECT ROWNUM CNT1,COLL,DEPT,K,A FROM (SELECT COLL ,DEPT,DECODE(CNT,1,'1학년',2,'2학년',3,'3학년',4,'4학년') K, DECODE(CNT,1,FRE,2,SUP,3,JUN,4,SEN) A FROM TEST11,(SELECT ROWNUM CNT FROM USER_TABLES WHERE ROWNUM
사원의 급여 합계 ,평균 및 부서별 직원수 뽑기
SELECT deptno , empno , DECODE(GROUP_ID(), 0, NVL(ename,'합계'), DECODE(GROUP_ID(), 2, '부서인원', NVL(ename,'평균'))) ename , DECODE(GROUP_ID(), 0, SUM(sal), DECODE(GROUP_ID(), 2, COUNT(ENAME) , ROUND(AVG(sal),2))) sal , GROUPING(deptno) g1 , GROUPING(empno) g2 , GROUPING_ID(empno, ename) g3 , GROUP_ID() g4 FROM scott.emp GROUP BY deptno, ROLLUP(deptno,DEPTNO,(empno, ename)) ORDER BY deptno, GROUP_ID()..
