OVER함수 PARTITION BY 절 조사
COD기준으로 DPT의 순위, 누적합, 최소값, 최대값을 표기, 바로상위행 NAM값, 바로하위행 NAM값을 COD, NAMS, DPT, RankV, MinV, MaxV, URowV, DRowV으로 작성 with TB AS ( SELECT 'DDDD' AS COD, 'aaaa' AS NAM, null AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'bbbb' AS NAM, '200' AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'cccc' AS NAM, '300' AS DPT FROM DUAL UNION ALL SELECT 'BBBB' AS COD, 'dddd' AS NAM, '100' AS DPT FROM DUAL UNI..
DB 정렬 관련 쿼리 작성
- 문제 SELECT 'DDDD' AS COD, '1111' AS NAM, null AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, 'aaaa' AS NAM, '600' AS DPT FROM DUAL UNION ALL SELECT 'CCCC' AS COD, 'xxxx' AS NAM, '300' AS DPT FROM DUAL UNION ALL SELECT 'AAAA' AS COD, '에이이에' AS NAM, '400' AS DPT FROM DUAL UNION ALL SELECT 'CCCC' AS COD, '일일일' AS NAM, '900' AS DPT FROM DUAL UNION ALL SELECT 'BBBB' AS COD, 'twotwo' AS NAM, null A..
특정문자가 포함된 스트링 문자열을 짤라, (COLUMN단위)로 만들기
--// 특정문자가 포함된 스트링 문자열을 짤라, 컬럼단위로 만들기 --// 시작과 끝을 특정문자열로 맞춘다. --// SUBSTR 부분을 함수화 시키면, 특정문자열의 지정된 항목의 값을 가져올수 있다. SELECT SUBSTR(str,INSTR(str,':',1,1)+1,INSTR(str,':',1,2)-INSTR(str,':',1,1)-1) as SUB1 , SUBSTR(str,INSTR(str,':',1,2)+1,INSTR(str,':',1,3)-INSTR(str,':',1,2)-1) as SUB2 , SUBSTR(str,INSTR(str,':',1,3)+1,INSTR(str,':',1,4)-INSTR(str,':',1,3)-1) as SUB3 , SUBSTR(str,INSTR(str,':',1,4..