데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

화면 배열 문제...

작성자

말짝이

작성일

2012-01-18 07:52ⓒ
2012-01-18 13:57ⓜ

조회수

6,363

with prj( pcd, amt )

(

select 1, 18000

union all select 2, 25000

incm( icd, pcd, idt, iamt )

(

select 1, 1, '2012-01-01', 2500

union all select 2, 2, '2012-01-04', 8000

union all select 3, 2, '2012-01-10', 15000

outcm( ocd, pcd, odt, oamt )

(

select 1, 1, '2012-01-03', 3000

union all select 2, 1, '2012-01-06', 6000

union all select 3, 2, '2012-01-15', 8000

union all select 4, 2, '2012-01-17', 15000

)

select

case when 1 = ROW_NUMBER() over ( partition by prj.pcd order by isnull( inout.icd, 1000) asc, isnull( inout.ocd, 1000 ) asc ) then cast( prj.pcd as varchar(10) ) else '' end as pcd

, case when 1 = ROW_NUMBER() over ( partition by prj.pcd order by isnull( inout.icd, 1000) asc, isnull( inout.ocd, 1000 ) asc ) then cast( prj.amt as varchar(10) ) else '' end as amt

, inout.idt, inout.iamt, inout.odt , inout.oamt

from

prj

inner join

(

select isnull( inl.pcd, outl.pcd ) as pcd,

inl.icd, inl.idt, inl.iamt, outl.ocd, outl.odt, outl.oamt

from

( select ROW_NUMBER() over ( partition by incm.pcd order by incm.icd asc) as rid

, incm.*

from incm

) as inl

full outer join

( select ROW_NUMBER() over ( partition by outcm.pcd order by outcm.ocd asc) as rid

, outcm.*

from outcm

) as outl on inl.pcd = outl.pcd and inl.rid = outl.rid

) as inout on prj.pcd = inout.pcd

order by prj.pcd, isnull( inout.icd, 1000) asc, isnull( inout.ocd, 1000 ) asc

우욱님이 2012-01-18 10:21에 작성한 댓글입니다. Edit Ｘ

No.

제목

작성자

작성일

조회

6316

데이터 타입 변경 시 에러 발생 [1]

moon

2012-01-31

9772

6315

에러가 자주 생겨요

정명규

2012-01-27

6154

6314

vb와 ms-sql연동

냠냠

2012-01-19

6723

6313

화면 배열 문제... [2]

말짝이

2012-01-18

6363

6311

쿼리로 ROWSPAN 효과내기 [1]

초보

2012-01-16

6403

6310

컬럼의 값을 카운트 하려면? [1]

초보자

2012-01-15

6311

6309

select 쿼리 질문입니다. [2]

유재영

2012-01-13

6937