꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 하나 물어볼게요~

by 미르 [SQLServer] [2012.11.30 14:11:52]

CREATE TABLE TABLE1(
    KEY        VARCHAR(10),
    VAR1     INT,
    VAR2    INT,
    VAR3    INT,
    VAR4    INT
)

CREATE TABLE TABLE2(
    KEY VARCHAR(10),
    RESULT1    INT,
    RESULT2     INT
)

위와같이 두개의 테이블이 존재한다고 가정할 때요

INSERT INTO TABLE1 (VAR1, VAR2, VAR3, VAR4) VALUES ('A', 2, 2, 5, 4)
INSERT INTO TABLE1 (VAR1, VAR2, VAR3, VAR4) VALUES ('B', 3, 2, 1, 5)
INSERT INTO TABLE1 (VAR1, VAR2, VAR3, VAR4) VALUES ('C', 1, 4, 2, 4)
INSERT INTO TABLE1 (VAR1, VAR2, VAR3, VAR4) VALUES ('D', 5, 5, 3, 3)
INSERT INTO TABLE1 (VAR1, VAR2, VAR3, VAR4) VALUES ('E', 3, 2, 1, 4)
INSERT INTO TABLE1 (VAR1, VAR2, VAR3, VAR4) VALUES ('F', 1, 2, 3, 4)

과 같이 TABLE1에 들어가 있을 경우

아래와 같은 결과를 얻고 싶어요

(KEY에 따라 작은 수 2개를 가지고 있는 필드 추출을 하고싶어요)

'A', 2, 2
'B', 1, 2
'C', 1, 2
'D', 3, 3
'E', 1, 2
'F', 1, 2

물론 순서가 바껴도 되요 (가령, 'B', 2, 1 이런 식으로요)

커서를 쓰면 될 것같긴 한데 그냥 SELECT 한번에 될까 싶어서 올려봅니다

by 아발란체 [2012.11.30 14:49:29]

--보다 간단히 짤 수 있을 것 같은데 멀리 돌아 짠 것 같네요.
SELECT
  key,
  SUM(DECODE(flag, 1, var)) AS var1,
  SUM(DECODE(flag, 0, var)) AS var2
FROM (
  SELECT
    key, var,
    MOD(ROW_NUMBER() OVER(ORDER BY KEY, VAR), 2) AS flag
  FROM (
    SELECT
      key,
      DECODE(lv, 1, var1, 2, var2, 3, var3, var4) AS var,
      RANK() OVER(PARTITION BY key ORDER BY DECODE(lv, 1, var1, 2, var2, 3, var3, var4)) AS rank_no
    FROM
      TABLE1, (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 4)
  ) WHERE
    rank_no <= 2
) GROUP BY
  key

by 아발란체 [2012.11.30 14:50:19]

근디 여기서 TABLE2는 목적 결과만 봤을 때 필요가 없는데용~ ! 데이타도 안 들어있고용.

by 마농 [2012.11.30 14:56:02]

SELECT key
     , LEAST(var1, var2, var3, var4) result1
     , GREATEST( LEAST(var1, var2, var3)
               , LEAST(var1, var2, var4)
               , LEAST(var1, var3, var4)
               , LEAST(var2, var3, var4)
               ) result2
  FROM table1
;

by 아발란체 [2012.11.30 15:09:33]

--다른 방법으로..
SELECT
  key, f1,
  MIN(val)
FROM (
  SELECT
    key,
    LEAST(VAR1, VAR2, VAR3, VAR4) AS F1,
    REGEXP_SUBSTR(
      REGEXP_REPLACE(VAR1||'-'||VAR2||'-'||VAR3||'-'||VAR4||'-', LEAST(VAR1, VAR2, VAR3, VAR4)||'-', '', 1, 1),
      '[0-9]{1,}', 1, LV
    ) AS VAL
  FROM
    TABLE1 T, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 3)
) GROUP BY
  key, f1
ORDER BY
  key

by 아발란체 [2012.11.30 15:10:39]

억!!! 마농님이 짧으신거 올리셨네요!!!... + ㅁ +)/ 왜 저렇게 생각을 못했을까.. ㅠ_ㅠ)ㆀ

by 신이만든짝퉁 [2012.11.30 17:54:30]

와~ 마농님 생각이 기발하시네요. ^^

by 미르 [2012.11.30 18:15:05]

답 달아주셔서 감사합니다~ mssql이라 오라클문법으로는 한계가 있네요... ㅠ_ㅠ

by 마농 [2012.12.01 00:49:06]

WITH table1 AS
(
SELECT 'A' pk, 2 v1, 2 v2, 5 v3, 4 v4
UNION ALL SELECT 'B', 3, 2, 1, 5
UNION ALL SELECT 'C', 1, 4, 2, 4
UNION ALL SELECT 'D', 5, 5, 3, 3
UNION ALL SELECT 'E', 3, 2, 1, 4
UNION ALL SELECT 'F', 1, 2, 3, 4
)
, copy_t AS
(
SELECT 1 lv
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
)
SELECT pk
     , MIN(CASE WHEN rn = 1 THEN v END) r1
     , MIN(CASE WHEN rn = 2 THEN v END) r2
  FROM (SELECT pk, lv, v
             , ROW_NUMBER() OVER(PARTITION BY pk ORDER BY v) rn
          FROM (SELECT pk, lv
                     , CASE lv
                       WHEN 1 THEN v1
                       WHEN 2 THEN v2
                       WHEN 3 THEN v3
                       WHEN 4 THEN v4
                        END v
                  FROM table1
                     , copy_t
                ) a
        ) b
 GROUP BY pk
;

by 마농 [2012.12.01 00:56:25]

SELECT pk
     , MIN(CASE WHEN rn = 1 THEN v END) r1
     , MIN(CASE WHEN rn = 2 THEN v END) r2
  FROM (SELECT pk, v
             , ROW_NUMBER() OVER(PARTITION BY pk ORDER BY v) rn
          FROM table1
         UNPIVOT (v for c IN (v1, v2, v3, v4)) a 
        ) a
 GROUP BY pk
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변