본문 바로가기

SQL/Oracle

 (5)
PRECEDING AND FOLLOWING 예제 (ORACLE) -- PREDECING AND FOLLOWING TEST WITH UNBOUNDED_TEST AS (SELECT '1' AS COL1, '' AS COL2 FROM DUAL UNION ALL SELECT '2' AS COL1, '1' AS COL2 FROM DUAL UNION ALL SELECT '3' AS COL1, '2' AS COL2 FROM DUAL UNION ALL SELECT '4' AS COL1, '3' AS COL2 FROM DUAL ) SELECT FIRST_VALUE(COL1) OVER (PARTITION BY COL1 ORDER BY CO1 ROWS UNBOUNDED PRECEDING) AS UNBOUNDED_PRECEDING ,FIRST_VALUE(COL1) OVER (ORDER B..
GRETEST/LEAST VS MAX/MIN 비교 (오라클) MIN, MAX 예제 --GREATEST, LEAST VS MAX, MIN TEST WITH GREATET_TEST AS (SELECT 1111 AS COL1 FROM DUAL UNION ALL SELECT 2222 AS COL2 FROM DUAL ), GREATEST_TEST_1 AS ( SELECT 3333 AS COL1 UNION ALL SELECT 4444 AS COL2 FROM DUAL) SELECT MAX(A.COL1), MAX(B.COL1), MIN(A.COL1), MIN(A.COL1) -- 각각 한개의 컬럼만을 비교할 수 있다. FROM GREATEST_TEST A, GREATEST_TEST_1 B GROUP BY A.COL1, B.COL1 결과값▼ MAX(A.COL1) MAX(B.COL1..
oracle_ unpivot 활용 --UNPIVOT TEST WITH TEST_TAB AS ( SELECT 'Y' AS COL1, 'Y' AS COL2, 'N' AS COL3, 'N' AS COL4, 'Y' AS COL5 FROM DUAL ) SELECT * FROM TEST_TAB UNPIVOT (SELECTED_CD FOR NM IN (COL1,COL2,COL3,COL4,COL5)) 여기서 'SELECTED_CD'와 'NM'은 행열을 바꾸기위해 빈 행에 채워지는 컬럼명 UNPIVOT함수를 통해서 쉽게 행열전환을 할 수 있다.
NVL vs NVL2 vs COALESCE vs NULLIF 구문차이정리 1. NVL NVL (exp1, exp2): exp1이 null이면 exp2로 바꾼다. 2. NVL2 NVL2(exp1,exp2,exp3) : exp1이 null이이 아니면 exp2로 바꾸고, exp1이 null이면 exp3로 바꾼다. exp1은 어떤타입의 데이터로 가질 수 있다. 3.COALESCE COALESCE (expr1, expr2, ..., exprn), for n>=3 [Oracle Documents] SELECT product_id, list_price, min_price, COALESCE(0.9*list_price, min_price, 5) "Sale" FROM product_information WHERE supplier_id = 102050 ORDER BY product_id, lis..
오라클 자주사용하는 힌트목록 정리(친절한 sql 튜닝) 0. 오라클 힌트를 사용하는 방법 - 힌트의 규칙 /*+ (힌트명)*/

티스토리툴바