데이터베이스 질문과 답변
아래와 같이 data가 있는데
table a에서 각seq별로 data를 6개씩 뽑을려면 쿼리를 어떻게 써야하나요?
비어있는 val는 0을 setting하고 seq는 해당 seq값을 세팅합니다.
고수님들 알려주시면 감사하겠습니다.(_ _)
기대결과
seq val
001 38
001 20
001 61
001 94
001 0
001 0
002 80
002 17
002 39
002 82
002 68
002 0
table a
seq val
001 38
001 20
001 61
001 94
002 80
002 17
002 39
002 82
002 68
WITH T (SEQ, VAL) AS (
SELECT '001', '38' FROM DUAL UNION ALL
SELECT '001', '20' FROM DUAL UNION ALL
SELECT '001', '61' FROM DUAL UNION ALL
SELECT '001', '94' FROM DUAL UNION ALL
SELECT '002', '80' FROM DUAL UNION ALL
SELECT '002', '17' FROM DUAL UNION ALL
SELECT '002', '39' FROM DUAL UNION ALL
SELECT '002', '82' FROM DUAL UNION ALL
SELECT '002', '68' FROM DUAL
)
SELECT SEQ, VAL
FROM (SELECT SEQ, VAL
, ROW_NUMBER() OVER(PARTITION BY SEQ ORDER BY VAL ) RN
FROM T
)
WHERE RN < 3
위 쿼리에서 RN <= 6 을 주면 아래와 같이 결과값이 나오는데요
선택되지 않는 값에다 seq는 그대로 val는 0을 세팅하고 싶습니다.
감사합니다.(_ _)
기대결과값
seq val -> seq val
001 38 001 38
001 20 001 20
001 61 001 61
001 94 001 94
002 80 001 0
002 17 001 0
002 39 002 80
002 82 002 17
002 68 002 39
002 82
002 68
002 0
with t as ( select '001' seq, '38' val from dual union all select '001' seq, '20' val from dual union all select '001' seq, '61' val from dual union all select '001' seq, '94' val from dual union all select '002' seq, '80' val from dual union all select '002' seq, '17' val from dual union all select '002' seq, '39' val from dual union all select '002' seq, '82' val from dual union all select '002' seq, '68' val from dual ) select seq, nvl(val,0) val from (select level lv from dual connect by level <= :rn) left outer join (select row_number() over(partition by seq order by rownum) rn , seq, val from t) partition by (seq) on (lv = rn) where lv <= :rn
) 버튼을 클릭하여 작성 하시면 됩니다.