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문으로 작성한다.

## 댓글0