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

혹시 이런게 가능할까요? 코드별로 무조건 2개 Row를 얻고 싶습니다

by 비니 [2014.01.14 13:13:36]

안녕하세요~

코드별로 무조건 2개 Row를 얻고 싶습니다

Data ========================
code date value
100 20140115 100
100 20140215 100
200 20140114 100
200 20140214 100
200 20140314 100
=============================
원하는 Row

100 20140115 100
100 20140215 100
200 20140114 100
200 20140214 100
=============================

Data ========================
code date value
100 20140115 100
200 20140114 100
200 20140214 100
200 20140314 100
=============================
원하는 Row

100 20140115 100
100
200 20140114 100
200 20140214 100
=============================

Data ========================
code date value
100 20140115 100
200 20140114 100
=============================
원하는 Row

100 20140115 100
100
200 20140114 100
200
=============================

Data ========================
code date value
100 20140115 100
=============================
원하는 Row

100 20140115 100
100
200
200
=============================

위처럼 무조건 100번 코드 2개 Row와 200번 코드 2개 Row를 가지고 싶습니다.
어디서부터 시작해야 할지 몇일째 고민중이네요..

도움 부탁드립니다~

좋은 하루 되세요~

by 용근님 [2014.01.14 15:30:49]

 
with t ( code    ,dateT     , value) as
(
select '100',    '20140115'  , '100' from dual union all
select '200',    '20140114'  , '100' from dual union all
select '200',    '20140214'  , '100' from dual union all
select '200',    '20140314'  , '100' from dual
)
,code ( rn, code ) as 
(
  select level rn
    , code
   from codeT
    , ( select level from connect by level <= 2 )
)

select *
 from (
    select t.*
       , row_number () over ( partition by code order by dateT asc ) rn
     from t
) a
, code b
where a.rn (+) = b.rn
 and a.code(+) = b.code

by 용근님 [2014.01.14 15:32:48]

left full outer join 도 검색해보세요

by 필상 [2014.01.14 16:33:49]

SELECT B.C1, A.C2
FROM ( SELECT A.C1, A.C2,
ROW_NUMBER() OVER(PARTITION BY C1 ORDER BY C2) AS TT
FROM ( --SELECT '100' AS C1, '20140101' AS C2 FROM DUAL UNION ALL
    --SELECT '100' AS C1, '20140102' AS C2 FROM DUAL UNION ALL
    --SELECT '200' AS C1, '20140101' AS C2 FROM DUAL UNION ALL
    --SELECT '200' AS C1, '20140102' AS C2 FROM DUAL UNION ALL
    SELECT '200' AS C1, '20140103' AS C2 FROM DUAL) A) A,
   ( SELECT '100' AS C1, 1 AS C2 FROM DUAL UNION ALL
SELECT '100' AS C1, 2 AS C2 FROM DUAL UNION ALL
SELECT '200' AS C1, 1 AS C2 FROM DUAL UNION ALL
SELECT '200' AS C1, 2 AS C2 FROM DUAL ) B
WHERE B.C1 = A.C1(+)
   AND B.C2 = A.TT(+)
ORDER BY 1, 2;

by 리오 [2014.01.14 17:53:01]

용근님, 필상님 정말 감사합니다~
많은 도움 주셔서 진심으로 감사드립니다 (__ )

댓글등록

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

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

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

데이터베이스 질문과 답변