데이터베이스 질문과 답변

SQL 질문좀 드립니다. 1 14 1,900

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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
--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]
?
1
2
3
4
5
6
7
8
9
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 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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입