본문 바로가기

분류 전체보기59

[Oracle/오라클] ROW_NUMBER, RANK, DENSE_RANK, RANK() WITHIN ROW_NUMBER 중복없는 순번(랭킹) 구하기 SELECT TEST_CD, KOR, ROW_NUMBER() OVER (ORDER BY KOR DESC) AS 순위 FROM TB_GRADE_07 ORDER BY KOR DESC; DENSE_RANK 같은 값이 있으면 중복 순위를 부여 SELECT TEST_CD, KOR, DENSE_RANK() OVER (ORDER BY KOR DESC) AS 순위 FROM TB_GRADE_07 ORDER BY KOR DESC; RANK() WITHIN - RANK()순위 부여와 같으며, GROUP() 함수의 '필드명'의 '값'이 몇번째에 위치하는 구함 SELECT RANK(96) WITHIN GROUP(ORDER BY KOR DESC) "96의 위치", RANK(90) .. 2021. 7. 17.
[Oracle/오라클] LSITAGG SELECT SALES_DT , LISTAGG(PRODUCT_NM, ',') WITHIN GROUP (ORDER BY PRODUCT_NM) AS ITEMS FROM TB_SALES GROUP BY SALES_DT; LISTAGG에는 중복제거 DISTINCT 사용불가로, 중복값이 조회된다 SELECT DISTINCT SALES_DT , LISTAGG(PRODUCT_NM, ',') WITHIN GROUP (ORDER BY PRODUCT_NM) OVER(PARTITION BY SALES_DT) AS PRODUCT_NM FROM TB_SALES; SELECT LISTAGG(PRODUCT_NM, ',') WITHIN GROUP (ORDER BY PRODUCT_NM) AS ITEMS FROM (select DIST.. 2021. 7. 16.
[Oracle/오라클] ROLLUP 소계 / 총계 표현하기 SELECT * FROM TB_POINT; SELECT NVL(CUSTOMER_CD,'총합') AS customer_cd, TO_CHAR(SUM(POINT),'999,999') AS 포인트합 FROM TB_POINT GROUP BY ROLLUP(CUSTOMER_CD) ORDER BY customer_cd; SELECT NVL(CUSTOMER_CD, '소계') 고객번호, TO_CHAR(SUM(POINT), '999,999') 포인트합, COUNT(*) 인원 FROM TB_POINT GROUP BY ROLLUP(CUSTOMER_CD); SELECT NVL(SALES_DT, '총합') AS SALES_DT, CASE WHEN SALES_DT IS NOT NULL AND PRODUCT_NM IS NULL THE.. 2021. 7. 16.
[Oracle/오라클] ADD_MONTHS, MONTH_BETWEEN ADD_MONTHS 지정일 기준 개월 수 추가또는 감소 계산 SELECT SYSDATE, ADD_MONTHS(SYSDATE, -3) AS "3_BF_MON", ADD_MONTHS(SYSDATE, 5) AS "5_AF_MON" FROM DUAL; MONTH_BETWEEN 기간을 월 기준으로 계산 SELECT SYSDATE, ADD_MONTHS(SYSDATE, -3) AS "3_BF_MON", ROUND(MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -3), SYSDATE), 2) MONTH_TERM, TO_DATE('20220408', 'YYYYMMDD') AS "STR_DATE", ROUND(MONTHS_BETWEEN(TO_DATE('20220408', 'YYYYMMDD'), SYSDAT.. 2021. 7. 11.

티스토리툴바