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

SQL 질문좀 드립니다.

by 당직하사 [2013.09.24 13:38:50]

데이터가 연결되는 숫자가 아닌 1,2,3,6,7,9,10,11 이런식으로 들어가 있는 경우
해당 연결되는 1, 2, 3의 MIN = 1, MAX = 3
6,7의 MIN=6, MAX=7
9,10,11의 MIN=9, MAX=11
의 데이터를 추출하는 SQL이 필요합니다.

도움 부탁드립니다.

SELECT 1 AS INPT FROM DUAL

UNION

SELECT 2 AS INPT FROM DUAL

UNION

SELECT 3 AS INPT FROM DUAL

UNION

SELECT 6 AS INPT FROM DUAL

UNION

SELECT 7 AS INPT FROM DUAL

UNION

SELECT 9 AS INPT FROM DUAL

UNION

SELECT 10 AS INPT FROM DUAL

UNION

SELECT 11 AS INPT FROM DUAL

by 아발란체 [2013.09.24 13:51:32]

 
WITH T AS (
  SELECT 1 AS INPT FROM DUAL
  UNION
  SELECT 2 AS INPT FROM DUAL
  UNION
  SELECT 3 AS INPT FROM DUAL
  UNION
  SELECT 6 AS INPT FROM DUAL
  UNION
  SELECT 7 AS INPT FROM DUAL
  UNION
  SELECT 9 AS INPT FROM DUAL
  UNION
  SELECT 10 AS INPT FROM DUAL
  UNION
  SELECT 11 AS INPT FROM DUAL
)
SELECT
  INPT
FROM (
  SELECT
    INPT,
    DECODE(LEAD(INPT) OVER(ORDER BY INPT ASC) - 1, INPT, 1, 0) AS MINC,
    DECODE(LAG(INPT) OVER(ORDER BY INPT ASC) + 1, INPT, 1, 0) AS MAXC
  FROM
    T
)
WHERE
  NOT(MINC = 1 AND MAXC = 1)

by 당직하사 [2013.09.24 13:56:19]

아발란체님 답변 감사드립니다.

추출되는 데이터가
MINC, MAXC
1 3
6 7
9 11
의 형태로는 안될까오?

by 아발란체 [2013.09.24 14:03:45]

WITH T AS (
  SELECT 1 AS INPT FROM DUAL
  UNION
  SELECT 2 AS INPT FROM DUAL
  UNION
  SELECT 3 AS INPT FROM DUAL
  UNION
  SELECT 6 AS INPT FROM DUAL
  UNION
  SELECT 7 AS INPT FROM DUAL
  UNION
  SELECT 9 AS INPT FROM DUAL
  UNION
  SELECT 10 AS INPT FROM DUAL
  UNION
  SELECT 11 AS INPT FROM DUAL
)
SELECT
  MAX(MINV) AS MINV,
  MAX(MAXV) AS MAXV
FROM (
  SELECT
    ROUND(ROWNUM / 2) AS GID,
    DECODE(MINC, 1, INPT, 0) AS MINV,
    DECODE(MAXC, 1, INPT, 0) AS MAXV
  FROM (
    SELECT
      INPT,
      DECODE(LEAD(INPT) OVER(ORDER BY INPT ASC) - 1, INPT, 1, 0) AS MINC,
      DECODE(LAG(INPT) OVER(ORDER BY INPT ASC) + 1, INPT, 1, 0) AS MAXC
    FROM
      T
    ORDER BY
      INPT
  )
  WHERE
    NOT(MINC = 1 AND MAXC = 1)
)
GROUP BY
  GID

by 아발란체 [2013.09.24 14:18:29]

--UNION 
--SELECT 17 AS INPT FROM DUAL
--
--이런 데이타가 추가 되면 
--이것은 최소 값으로도 볼 수 없고, 최대 값으로도 볼 수 없기 때문에
--이런 데이타는 삭제 처리가 필요 할 것 같습니다.




WITH T AS (
 SELECT 1 AS INPT FROM DUAL
 UNION
 SELECT 2 AS INPT FROM DUAL
 UNION
 SELECT 3 AS INPT FROM DUAL
 UNION
 SELECT 6 AS INPT FROM DUAL
 UNION
 SELECT 7 AS INPT FROM DUAL
 UNION
 SELECT 9 AS INPT FROM DUAL
 UNION
 SELECT 10 AS INPT FROM DUAL
 UNION
 SELECT 11 AS INPT FROM DUAL
 UNION
 SELECT 16 AS INPT FROM DUAL
)
SELECT
 MAX(MINV) AS MINV,
 MAX(MAXV) AS MAXV
FROM (
 SELECT
 ROUND(ROWNUM / 2) AS GID,
 DECODE(MINC, 1, INPT, 0) AS MINV,
 DECODE(MAXC, 1, INPT, 0) AS MAXV
 FROM (
 SELECT
 INPT,
 DECODE(LEAD(INPT) OVER(ORDER BY INPT ASC) - 1, INPT, 1, 0) AS MINC,
 DECODE(LAG(INPT) OVER(ORDER BY INPT ASC) + 1, INPT, 1, 0) AS MAXC
 FROM
 T
 ORDER BY
 INPT
 )
 WHERE
 NOT(MINC = 1 AND MAXC = 1) 
 OR (MINC = 0 AND MAXC = 0) 
)
WHERE
 MINV != 0 OR MAXV != 0
GROUP BY
 GID
ORDER BY
 GID
;

by 용근님 [2013.09.24 13:58:30]

Select min(inpt) from ( select 별표 from t order by inpt ) group by inpt - 1

by 용근님 [2013.09.24 13:58:53]

Group by inpt - rownum

by 당직하사 [2013.09.24 14:06:34]

용근님
답변 감사드립니다.

by 우리집아찌 [2013.09.24 14:18:08]

어렵네요.. 마농님 퀴즈보고 이해했어요.. ㅎㅎ

by 마농 [2013.09.24 14:14:37]

SELECT MIN(inpt) inpt_s
     , MAX(inpt) inpt_e
  FROM (SELECT inpt
             , ROW_NUMBER() OVER(ORDER BY inpt) rn
          FROM t
        )
 GROUP BY inpt - rn
 ORDER BY inpt_s
;

by 아발란체 [2013.09.24 14:24:00]

역시 마농님 킹왕짱... 대박 간결하고 이해하기도 쉽네요.
와... 또 배우네요.

by 마농 [2013.09.24 14:51:21]

http://oracleclub.com/article/45713
http://oracleclub.com/lecture/2194

by 당직하사 [2013.09.24 15:12:50]

마농님
답변 감사드려요!!
ㅎㅎ

by 용근님 [2013.09.24 14:33:50]

아 핸드폰에서 적었더니

WITH T AS (
SELECT 1 AS INPT FROM DUAL
UNION
SELECT 2 AS INPT FROM DUAL
UNION
SELECT 3 AS INPT FROM DUAL
UNION
SELECT 6 AS INPT FROM DUAL
UNION
SELECT 7 AS INPT FROM DUAL
UNION
SELECT 9 AS INPT FROM DUAL
UNION
SELECT 10 AS INPT FROM DUAL
UNION
SELECT 11 AS INPT FROM DUAL
UNION
SELECT 16 AS INPT FROM DUAL
)
Select min(inpt)
, MAX(INPT)
from ( select *
from t
order by inpt
)
group by inpt - ROWNUM

by 아발란체 [2013.09.24 14:48:52]

와........
이제 이해를.... OTL

댓글등록

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

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

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

데이터베이스 질문과 답변