Search

'Rownum'에 해당되는 글 3건

  1. 2018.07.03 [ORACLE]ROW_NUMBER 순서
  2. 2016.10.21 [MYSQL]ROWNUM
  3. 2009.11.04 [ORACLE] 구분자_LIST형_맵핑형_조합형쿼리

[ORACLE]ROW_NUMBER 순서

Database 2018.07.03 08:29 Posted by 애플자라
SELECT 
   ROW_NUMBER() OVER (ORDER BY NAME ASC) AS EMS_M_ID,
   NAME AS EMS_M_NAME,
   TRIM(REPLACE(MOBILE,'-','')) AS EMS_M_PHONE
FROM APLUSORA.TEMP_N_BOARD_EVENT
ORDER BY NAME ASC

 

'Database' 카테고리의 다른 글

[ORACLE]ROW_NUMBER 순서  (0) 2018.07.03
[Oracle] 누적접수건수  (0) 2018.06.27
[Oracle] 다중 LIKE(REGEXP_LIKE)  (0) 2018.06.12
[ORACLE]COUNT DECODE  (0) 2018.06.04
[ORACLE]WMSYS.WM_CONCAT 월별데이터 합치기  (0) 2018.04.06
[ORACLE]그룹함수로 중복된 컬럼 제거하기  (0) 2017.11.08

[MYSQL]ROWNUM

Database 2016.10.21 09:03 Posted by 애플자라

SELECT @RNUM := @RNUM + 1 AS ROWNUM,
       EVENT_SCHEMA,
       EVENT_NAME,
       INTERVAL_FIELD,
       EVENT_DEFINITION,
       LAST_EXECUTED
FROM information_schema.EVENTS, (SELECT @RNUM := 0) R
ORDER BY LAST_EXECUTED DESC

'Database' 카테고리의 다른 글

[MYSQL]패스워드 변경  (0) 2016.11.11
[MYSQL]multiple instances(mysqld_multi)  (0) 2016.11.08
[MYSQL]ROWNUM  (0) 2016.10.21
[MYSQL]Fabric 설치 및 HA 구성  (0) 2016.10.17
[ORACLE]ORU-10027: buffer overflow  (0) 2016.10.10
[MYSQL]mysqldump 백업  (0) 2016.09.29
TAG MySQL, RNUM, Rownum

[ORACLE] 구분자_LIST형_맵핑형_조합형쿼리

Database 2009.11.04 18:10 Posted by 애플자라

* TB_INST_INPUT테이블에 SUBS_VALUE 데이터가 |구분자로 값이 들어오는 경우

-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 각각 매핍변수로 선언

SELECT SUB1,SUB2,SUB3,SUB4,SUB5,SUB6,SUB7,SUB8,SUB9,SUB10,SUB11,SUB12,SUB13,SUB14,
SUB15
    FROM(SELECT
            MAX(DECODE(rowno,1,aa)) SUB1
           ,MAX(DECODE(rowno,2,aa)) SUB2
           ,MAX(DECODE(rowno,3,aa)) SUB3
           ,MAX(DECODE(rowno,4,aa)) SUB4
           ,MAX(DECODE(rowno,5,aa)) SUB5
           ,MAX(DECODE(rowno,6,aa)) SUB6
           ,MAX(DECODE(rowno,7,aa)) SUB7
           ,MAX(DECODE(rowno,8,aa)) SUB8
           ,MAX(DECODE(rowno,9,aa)) SUB9
           ,MAX(DECODE(rowno,10,aa)) SUB10
           ,MAX(DECODE(rowno,11,aa)) SUB11
           ,MAX(DECODE(rowno,12,aa)) SUB12
           ,MAX(DECODE(rowno,13,aa)) SUB13
           ,MAX(DECODE(rowno,14,aa)) SUB14
           ,MAX(DECODE(rowno,15,aa)) SUB15
        FROM(
             SELECT SUBSTR( RCVDATA,
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1,
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) -
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa,
                    ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE ||'|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT
                       --WHERE INSTID = '@{EMS_M_ID}'
                       )
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) -
                    LENGTH(REPLACE(RCVDATA, '|')) - 1
        )
        WHERE ROWNUM <= '15'
    )
WHERE ROWNUM = 1


-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 한개의 데이터로 조합


SELECT a1||a2||a3||a4||a5||a6||a7||a8||a9||a10||a11||a12||a13||a14  totaldata
    FROM(SELECT MAX(DECODE(rowno,1,aa)) a1
           ,MAX(DECODE(rowno,2,aa)) a2
           ,MAX(DECODE(rowno,3,aa)) a3
           ,MAX(DECODE(rowno,4,aa)) a4
           ,MAX(DECODE(rowno,5,aa)) a5
           ,MAX(DECODE(rowno,6,aa)) a6
           ,MAX(DECODE(rowno,7,aa)) a7
           ,MAX(DECODE(rowno,8,aa)) A8
           ,MAX(DECODE(rowno,9,aa)) a9
           ,MAX(DECODE(rowno,10,aa)) a10
           ,MAX(DECODE(rowno,11,aa)) a11
           ,MAX(DECODE(rowno,12,aa)) a12
           ,MAX(DECODE(rowno,13,aa)) a13
           ,MAX(DECODE(rowno,14,aa)) a14
        FROM(SELECT SUBSTR( RCVDATA,
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1,
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) -
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
                    ,ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT)
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) -
                    LENGTH(REPLACE(RCVDATA, '|')) - 1)
    WHERE ROWNUM <= '14'
    )
   
   
-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 리스트 데이터로 조합   

SELECT SUBSTR( RCVDATA,
INSTR (RCVDATA, '|', 1, LEVEL) + 1,
INSTR (RCVDATA, '|', 1, LEVEL + 1) -
INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA FROM TB_INST_INPUT)
CONNECT BY LEVEL<= LENGTH(RCVDATA) -
LENGTH(REPLACE(RCVDATA, '|')) - 1;

참조 - 이동준형 마음ㅋ