핵심은 두가지가 될것 같으네요.
1. recursive하게 number를 만드는 것
2. 각 테이블에 더미로 rownumber를 만드는 것
그 다음은 조인이 될것 같으네요.
참고요
with
nums as (select 1 as r union all select r + 1 from nums where r < 10),
t1 as (
Select '1' as 현장코드, '작업자1' as 작업자, '250000' as 인건비 union all
Select '1' as 현장코드, '작업자2' as 작업자, '150000' as 인건비 ),
t2 as (Select '1' as 현장코드, '상도' as 구분, '재료1' as 재료명 union all
Select '1' as 현장코드, '중도(내화)' as 구분, '재료2' as 재료명 union all
Select '1' as 현장코드, '하도' as 구분, '재료3' as 재료명 ),
t3 as ( Select '1' as 현장코드, '스카이' as 품목, '경남스카이' as 업체명 union all
Select '1' as 현장코드, '스카이' as 품목, '경북스카이' as 업체명 union all
Select '1' as 현장코드, '스카이' as 품목, '충청스카이' as 업체명 union all
Select '1' as 현장코드, '스카이' as 품목, '서울' as 업체명 ),
t4 as ( Select '1' as 현장코드, '편의점' as 지출처, '홍길동' as 사용자 )
select
*
from
nums a
left outer join (select
row_number() over (order by (select 1)) as r, *
from t1) a1
on a.r = a1.r
from t2) a2
on a.r = a2.r
from t3 ) a3
on a.r = a3.r
from t4 ) a4
on a.r = a4.r
where
isnull(a1.r, 0) + isnull(a2.r, 0) + isnull(a3.r, 0) + isnull(a4.r, 0) > 0
Drop your files here, or click the button to the left.
Maximum File Size : 0MB (Allowed extentsions : *.*)
Uploading... (0%)
Keep me signed in.
핵심은 두가지가 될것 같으네요.
1. recursive하게 number를 만드는 것
2. 각 테이블에 더미로 rownumber를 만드는 것
그 다음은 조인이 될것 같으네요.
참고요
with
nums as (select 1 as r union all select r + 1 from nums where r < 10),
t1 as (
Select '1' as 현장코드, '작업자1' as 작업자, '250000' as 인건비 union all
Select '1' as 현장코드, '작업자2' as 작업자, '150000' as 인건비 ),
t2 as (Select '1' as 현장코드, '상도' as 구분, '재료1' as 재료명 union all
Select '1' as 현장코드, '중도(내화)' as 구분, '재료2' as 재료명 union all
Select '1' as 현장코드, '하도' as 구분, '재료3' as 재료명 ),
t3 as ( Select '1' as 현장코드, '스카이' as 품목, '경남스카이' as 업체명 union all
Select '1' as 현장코드, '스카이' as 품목, '경북스카이' as 업체명 union all
Select '1' as 현장코드, '스카이' as 품목, '충청스카이' as 업체명 union all
Select '1' as 현장코드, '스카이' as 품목, '서울' as 업체명 ),
t4 as ( Select '1' as 현장코드, '편의점' as 지출처, '홍길동' as 사용자 )
select
*
from
nums a
left outer join (select
row_number() over (order by (select 1)) as r, *
from t1) a1
on a.r = a1.r
left outer join (select
row_number() over (order by (select 1)) as r, *
from t2) a2
on a.r = a2.r
left outer join (select
row_number() over (order by (select 1)) as r, *
from t3 ) a3
on a.r = a3.r
left outer join (select
row_number() over (order by (select 1)) as r, *
from t4 ) a4
on a.r = a4.r
where
isnull(a1.r, 0) + isnull(a2.r, 0) + isnull(a3.r, 0) + isnull(a4.r, 0) > 0