[SQL] PNU-XY 테이블 문제

========================= 테이블 조인 ==========================​​

-- 3테이블 JOIN + 초성

​INSERT INTO XX
SELECT a.a2, b.b1, fn_choSearch(substr(b.b3, 1, 1))
FROM  aa a  JOIN bb b  ON SUBSTR(a.a1, 1, 2) = b.b1
                  JOIN cc c   ON c.c1 = b.b1;
          

​

SELECT * FROM bb;

select bb.b1||bb.b2 from bb;

​

-- 정렬

SELECT b.b1||c.c2, b.b3, c.c3

FROM bb b, cc c

WHERE b.b1||b.b2 = c.c1||c.c2;

​

​

-- 3 inner join

SELECT *

FROM aa a, (

          SELECT b.b1||c.c2 pnu, b.b3, c.c3

           FROM bb b, cc c

           WHERE b.b1||b.b2 = c.c1||c.c2

          ) j

WHERE a.a1 = j.pnu;

​

-- 3 inner join

SELECT *

FROM aa a join (

              SELECT b.b1||c.c2 pnu, b.b3, c.c3, fn_choSearch(substr(b.b3,1, 1))

               FROM bb b join cc c

               on b.b1||b.b2 = c.c1||c.c2

               ) j

on a.a1 = j.pnu;

 

 

 

SELECT b.b1||c.c2, null, c.c3

FROM bb b

WHERE EXISTS (SELECT 1

              FROM cc c

              WHERE b.b1||b.b2 = c.c1||c.c2);

 

========================= 테이블 생성 ==========================

SELECT

   *

FROM

   v$version;

   

DROP TABLE cc;

DROP TABLE bb;  

DROP TABLE aa;

​

CREATE TABLE aa (

   a1 VARCHAR2(20),

   a2 VARCHAR2(20),

   a3 VARCHAR2(20)

);

​

CREATE TABLE bb (

   b1 VARCHAR2(20),

   b2 VARCHAR2(20),

   b3 VARCHAR2(20)

);

​

CREATE TABLE cc (

   c1 VARCHAR2(20),

   c2 VARCHAR2(20),

   c3 VARCHAR2(20)

);

​

CREATE TABLE aa (

   a1 VARCHAR2(20),

   a2 VARCHAR2(20),

   a3 VARCHAR2(20),

   a4 VARCHAR2(20),

   a5 VARCHAR2(20)

);

​

CREATE TABLE bb (

   b1 VARCHAR2(20),

   b2 VARCHAR2(20),

   b3 VARCHAR2(20),

   b4 VARCHAR2(20),

   b5 VARCHAR2(20)

);

​

CREATE TABLE cc (

   c1 VARCHAR2(20),

   c2 VARCHAR2(20),

   c3 VARCHAR2(20),

   c4 VARCHAR2(20),

   c5 VARCHAR2(20)

);

​

​

TRUNCATE TABLE aa;

TRUNCATE TABLE bb;

TRUNCATE TABLE cc;

​

INSERT INTO aa

select '0000', 'a0', 'aa0', 'aaa0', 'z0' from dual union all

select '1212', 'a1', 'aa1', 'aaa1', 'z1' from dual union all

select '3434', 'a2', 'aa2', 'aaa2', 'z2' from dual union all

select '5656', 'a3', 'aa3', 'aaa3', 'z3' from dual union all

select '7878', 'a4', 'aa4', 'aaa4', 'z4' from dual union all

select '9090', 'a5', 'aa5', 'aaa5', 'z5' from dual

;

INSERT INTO bb

select '12', '12', 'b1', 'bb1', 'bbb1' from dual union all

select '56', '56', 'b3', 'bb3', 'bbb3' from dual union all

select '34', '34', 'b2', 'bb2', 'bbb2' from dual union all

select '78', '78', 'b4', 'bb4', 'bbb4' from dual

;

INSERT INTO cc

select '56', '56', 'c3', 'cc3', 'ccc3' from dual union all

select '12', '12', 'c1', 'cc1', 'ccc1' from dual union all

select '34', '34', 'c2', 'cc2', 'ccc2' from dual

;

​

INSERT INTO aa

select '0000', 'a0', 'aa0' from dual union all

select '1212', 'a1', 'aa1' from dual union all

select '3434', 'a2', 'aa2' from dual union all

select '5656', 'a3', 'aa3' from dual union all

select '7878', 'a4', 'aa4' from dual union all

select '9090', 'a5', 'aa5' from dual

;

​

INSERT INTO bb

select '12', '12', 'b1' from dual union all

select '56', '56', 'b3' from dual union all

select '34', '34', 'b2' from dual union all

select '78', '78', 'b4' from dual

;

INSERT INTO cc

select '56', '56', 'c3' from dual union all

select '12', '12', 'c1' from dual union all

select '34', '34', 'c2' from dual

commit;

select b3, b4

from bb;

​

​========================= 테이블 조회 ==========================

-- 특정 행번호 전후 조회

SELECT ROWNUM, k.* FROM (select * from bb) k;

​

rollback;

commit;

​

-- 특정 행번호 전후 행 제거

DELETE FROM bb

WHERE ROWID in

(SELECT RID FROM (SELECT ROWNUM rn, ROWID RID FROM bb) WHERE rn > 4)

;

​

SELECT *

FROM (

SELECT rownum NUM, N.*    --(1) rownum 붙은 걸 FROM에 두고

FROM (select * from bb) N

)

WHERE NUM BETWEEN 6 AND 10;

​

​

SELECT *

FROM (

SELECT rownum NUM, N.*    --(1) rownum 붙은 걸 FROM에 두고

FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) N

)

WHERE NUM BETWEEN 6 AND 10;   --(2) WHERE에서 1번 외의 시작 번호로 조건 가능

 

​

 

​​========================= 초성 추출 ==========================

​--fn_choSearch(substr('홍길동',1, 1))

create or replace function fn_choSearch( str in varchar2) return varchar2

is

  returnStr varchar2(100);  

  cnt number := 0;  

  tmpStr varchar2(10);

begin  

     if str is null then

     return '';

     end if;  

 

     cnt := length(str);  

 

     for i in 1 .. cnt

     loop

       tmpStr := substr(str,i,1);        

     returnStr := returnStr ||

     case when tmpStr < 'ㄱ' then substr(tmpStr, 1, 1)

           when ascii('ㄱ') <= ascii(tmpStr) and ascii(tmpStr) <= ascii('ㅎ') then chr(ascii(tmpStr))

           when tmpStr < '나' then 'ㄱ'

           when tmpStr < '다' then 'ㄴ'

           when tmpStr < '라' then 'ㄷ'

           when tmpStr < '마' then 'ㄹ'

           when tmpStr < '바' then 'ㅁ'

           when tmpStr < '사' then 'ㅂ'

           when tmpStr < '아' then 'ㅅ'

           when tmpStr < '자' then 'ㅇ'

           when tmpStr < '차' then 'ㅈ'

           when tmpStr < '카' then 'ㅊ'

           when tmpStr < '타' then 'ㅋ'

           when tmpStr < '파' then 'ㅌ'

           when tmpStr < '하' then 'ㅍ'

           else 'ㅎ'

     end;  

     end loop;

     return returnStr;

end;

​

​

select substr('홍길동',1, 1) from dual;

select

​

FROM dual;

​

WITH t AS

(

SELECT 1 id, '홍길동' nm FROM dual

UNION ALL SELECT 2, '마농' FROM dual

UNION ALL SELECT 3, '구루비' FROM dual

UNION ALL SELECT 4, '허거덩' FROM dual

)

SELECT id, nm

    , LISTAGG(x) WITHIN GROUP(ORDER BY lv) nm1

 FROM (SELECT id, lv, nm

            , SUBSTR('ㄱㄲㄴㄷㄸㄹㅁㅂㅃㅅㅆㅇㅈㅉㅊㅋㅌㅍㅎ'

              , FLOOR((ASCII(UNISTR(SUBSTR(nm, lv, 1))) - 44032) / (28*21)) + 1

              , 1) x

         FROM t

            , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)

        WHERE lv <= LENGTH(nm)

       )

GROUP BY id, nm

;

​

--INSERT INTO (1, 2, 3, 4, 5)

--select '', '', '', '', '' from dual union all

--select '', '', '', '', '' from dual union all

--select '', '', '', '', '' from dual union all

--select '', '', '', '', '' from dual union all

--select '', '', '', '', '' from dual

--;

​

--P : PNU, X, Y, Null, Null

--R : PN, U, X, Y

--B : PN, U, X, Y​

​

​

--create or replace function fn_choSearch( str in varchar2) return varchar2

--is

--   returnStr varchar2(100);  

--   cnt number := 0;  

--   tmpStr varchar2(10);

--begin  

--      if str is null then

--      return '';

--      end if;  

--

--      cnt := length(str);  

--

--      for i in 1 .. cnt

--      loop

--        tmpStr := substr(str,i,1);        

--      returnStr := returnStr ||

--      case when tmpStr < 'ㄱ' then substr(tmpStr, 1, 1)

--            when ascii('ㄱ') <= ascii(tmpStr) and ascii(tmpStr) <= ascii('ㅎ') then chr(ascii(tmpStr))

--            when tmpStr < '나' then 'ㄱ'

--            when tmpStr < '다' then 'ㄴ'

--            when tmpStr < '라' then 'ㄷ'

--            when tmpStr < '마' then 'ㄹ'

--            when tmpStr < '바' then 'ㅁ'

--            when tmpStr < '사' then 'ㅂ'

--            when tmpStr < '아' then 'ㅅ'

--            when tmpStr < '자' then 'ㅇ'

--            when tmpStr < '차' then 'ㅈ'

--            when tmpStr < '카' then 'ㅊ'

--            when tmpStr < '타' then 'ㅋ'

--            when tmpStr < '파' then 'ㅌ'

--            when tmpStr < '하' then 'ㅍ'

--            else 'ㅎ'

--      end;  

--      end loop;

--      return returnStr;

--end;

​​