반응형
1월달 ~ 12월달까지의 데이타를 한번 쿼리를 던져서 가져오는 방법
(연구중)
Form 뒤의 테이블 부분을 서브쿼리를 이용하여 작성한다.
Select substring(a.opendate, 7, 2) as tdate, sum(a.m1) as m1, sum(a.m2) as m2, sum(a.m3) as m3, sum(a.m4) as m4, sum(a.m5) as m5, sum(a.m6) as m6, sum(a.m7) as m7
, sum(a.m7) as m7, sum(a.m8) as m8, sum(a.m9) as m9, sum(a.m10) as m10, sum(a.m11) as m11, sum(a.m12) as m12
From (
Select opendate, t_target as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200801%'
and gubun ='01'
Union all
Select opendate, 0 as m1, t_target as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200802%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, t_target as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200803%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, t_target as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200804%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, t_target as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200805%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, t_target as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200806%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, t_target as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200807%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, t_target as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200808%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, t_target as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200809%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, t_target as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200810%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, t_target as m11, 0 as m12
from p_store_DSR
where opendate like '200811%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, t_target as m12
from p_store_DSR
where opendate like '200812%'
and gubun ='01' ) a
Group by substring(a.opendate, 7, 2)
아래 반복되는 부분을 For문으로 작성한다.
(연구중)
Form 뒤의 테이블 부분을 서브쿼리를 이용하여 작성한다.
Select substring(a.opendate, 7, 2) as tdate, sum(a.m1) as m1, sum(a.m2) as m2, sum(a.m3) as m3, sum(a.m4) as m4, sum(a.m5) as m5, sum(a.m6) as m6, sum(a.m7) as m7
, sum(a.m7) as m7, sum(a.m8) as m8, sum(a.m9) as m9, sum(a.m10) as m10, sum(a.m11) as m11, sum(a.m12) as m12
From (
Select opendate, t_target as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200801%'
and gubun ='01'
Union all
Select opendate, 0 as m1, t_target as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200802%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, t_target as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200803%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, t_target as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200804%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, t_target as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200805%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, t_target as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200806%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, t_target as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200807%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, t_target as m8, 0 as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200808%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, t_target as m9, 0 as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200809%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, t_target as m10, 0 as m11, 0 as m12
from p_store_DSR
where opendate like '200810%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, t_target as m11, 0 as m12
from p_store_DSR
where opendate like '200811%'
and gubun ='01'
Union all
Select opendate, 0 as m1, 0 as m2, 0 as m3, 0 as m4, 0 as m5, 0 as m6, 0 as m7, 0 as m8, 0 as m9, 0 as m10, 0 as m11, t_target as m12
from p_store_DSR
where opendate like '200812%'
and gubun ='01' ) a
Group by substring(a.opendate, 7, 2)
아래 반복되는 부분을 For문으로 작성한다.