데이터베이스 질문과 답변

MSSQL 5배수 SQL 질문이요. 0 3 2,493

by 김준 [SQLServer] [2016.11.10 14:08:44]

안녕하세요.

MSSQL 2010 SQL 질문이요. 

KEY 5배수
KEY1 1
KEY2 1
KEY3 1
KEY4 1
KEY5 1
KEY6 2
KEY7 2
KEY8 2
KEY9 2
공백 2
KEY10 3
KEY11 3
KEY12 3
KEY13 3
KEY14 3
KEY15 3
공백 3
공백 3
공백 3
공백 3
KEY16 4
KEY17 4
KEY18 4
공백 4
공백 4
KEY19 5
KEY20 5
…..

위와 같이 5배수 쿼리에 공백을 삽입해서 5배수를 만들라고 합니다.

가이드 도움 부탁 드립니다.  

 

 

by 마농 [2016.11.10 16:03:06] 
WITH t AS
(
SELECT 'KEY1' id, 1 no
UNION ALL SELECT 'KEY2' , 1
UNION ALL SELECT 'KEY3' , 1
UNION ALL SELECT 'KEY4' , 1
UNION ALL SELECT 'KEY5' , 1
UNION ALL SELECT 'KEY6' , 2
UNION ALL SELECT 'KEY7' , 2
UNION ALL SELECT 'KEY8' , 2
UNION ALL SELECT 'KEY9' , 2
UNION ALL SELECT 'KEY10', 3
UNION ALL SELECT 'KEY11', 3
UNION ALL SELECT 'KEY12', 3
UNION ALL SELECT 'KEY13', 3
UNION ALL SELECT 'KEY14', 3
UNION ALL SELECT 'KEY15', 3
UNION ALL SELECT 'KEY16', 4
UNION ALL SELECT 'KEY17', 4
UNION ALL SELECT 'KEY18', 4
UNION ALL SELECT 'KEY19', 5
UNION ALL SELECT 'KEY20', 5
)
, tmp AS
(
SELECT no
     , 1 rn
     , CEILING(COUNT(*) / 5.) * 5 max_rn
  FROM t
 GROUP BY no
 UNION ALL
SELECT no
     , rn + 1 rn
     , max_rn
  FROM tmp
 WHERE rn + 1 <= max_rn
)
SELECT b.id
     , a.no
  FROM tmp a
  LEFT OUTER JOIN
       (SELECT id, no
             , ROW_NUMBER() OVER(PARTITION BY no ORDER BY id) rn
          FROM t
        ) b
    ON a.no = b.no
   AND a.rn = b.rn
 ORDER BY a.no, a.rn
;
WITH t AS
(
SELECT 'KEY1' id, 1 no
UNION ALL SELECT 'KEY2' , 1
UNION ALL SELECT 'KEY3' , 1
UNION ALL SELECT 'KEY4' , 1
UNION ALL SELECT 'KEY5' , 1
UNION ALL SELECT 'KEY6' , 2
UNION ALL SELECT 'KEY7' , 2
UNION ALL SELECT 'KEY8' , 2
UNION ALL SELECT 'KEY9' , 2
UNION ALL SELECT 'KEY10', 3
UNION ALL SELECT 'KEY11', 3
UNION ALL SELECT 'KEY12', 3
UNION ALL SELECT 'KEY13', 3
UNION ALL SELECT 'KEY14', 3
UNION ALL SELECT 'KEY15', 3
UNION ALL SELECT 'KEY16', 4
UNION ALL SELECT 'KEY17', 4
UNION ALL SELECT 'KEY18', 4
UNION ALL SELECT 'KEY19', 5
UNION ALL SELECT 'KEY20', 5
)
, tmp AS
(
SELECT id, no
     , rn
     , CASE rn WHEN cnt THEN CEILING(cnt / 5.) * 5 ELSE rn END max_rn
  FROM (SELECT id, no
             , ROW_NUMBER() OVER(PARTITION BY no ORDER BY id) rn
             , COUNT(*) OVER(PARTITION BY no) cnt
          FROM t
        ) a
 UNION ALL
SELECT null id
     , no
     , rn + 1 rn
     , max_rn
  FROM tmp
 WHERE rn + 1 <= max_rn
)
SELECT id, no
  FROM tmp
 ORDER BY no, rn
;

 

by 우리집아찌 [2016.11.10 16:10:54]
공백이 들어있는 위치랑 갯수의 의미를 모르겠어요
by 김준 [2016.11.10 16:21:35]

마농님~!! 고맙습니다.  많은 도움이 되었네요..^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입