select e.PACK_NAME,
d.up_region_no,
count(distinct a1.split_id) as 一到四月,
count(distinct a2.split_id) as 五月
from (select *
from v_tcm_user_prod
where bureau_no = '0013701'
and oper_date > =
to_date('2006-12-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and oper_date < =
to_date('2007-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) a1,
(select *
from v_tcm_user_prod
where bureau_no = '0013701'
and oper_date > =
to_date('2007-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and oper_date < =
to_date('2007-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) a2,
v_tcm_user b,
qh_twm_region d,
v_tpm_pack e
where e.PACK_ID in (‘1’,‘2’,‘3’)
and a2.PACK_ID = e.PACK_ID
and a1.PACK_ID = e.PACK_ID
and A1.USER_ID = B.USER_ID
and A2.USER_ID = B.USER_ID
and B.REGION_NO = D.REGION_NO
and b.STATE = '1'
and b.USER_seq = '0'
and b.finish_flag = '1'
and a1.state = '1'
and a1.finish_flag = '1'
and a2.state = '1'
and a2.finish_flag = '1'
group by e.PACK_NAME, d.up_region_no此SQL的问题是 a1,a2 对应的相同的表v_tcm_user b, a1,a2时间区间不同是取不出来数据的,如果a1,a2 有相同区间 只能取到, a1,a2相交的部分 请问大家有什么解决办法?谢谢了
d.up_region_no,
count(distinct a1.split_id) as 一到四月,
count(distinct a2.split_id) as 五月
from (select *
from v_tcm_user_prod
where bureau_no = '0013701'
and oper_date > =
to_date('2006-12-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and oper_date < =
to_date('2007-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) a1,
(select *
from v_tcm_user_prod
where bureau_no = '0013701'
and oper_date > =
to_date('2007-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and oper_date < =
to_date('2007-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) a2,
v_tcm_user b,
qh_twm_region d,
v_tpm_pack e
where e.PACK_ID in (‘1’,‘2’,‘3’)
and a2.PACK_ID = e.PACK_ID
and a1.PACK_ID = e.PACK_ID
and A1.USER_ID = B.USER_ID
and A2.USER_ID = B.USER_ID
and B.REGION_NO = D.REGION_NO
and b.STATE = '1'
and b.USER_seq = '0'
and b.finish_flag = '1'
and a1.state = '1'
and a1.finish_flag = '1'
and a2.state = '1'
and a2.finish_flag = '1'
group by e.PACK_NAME, d.up_region_no此SQL的问题是 a1,a2 对应的相同的表v_tcm_user b, a1,a2时间区间不同是取不出来数据的,如果a1,a2 有相同区间 只能取到, a1,a2相交的部分 请问大家有什么解决办法?谢谢了
d.up_region_no,
count(distinct case when (a.oper_date > =
to_date('2006-12-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and a.oper_date < =
to_date('2007-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) then a.split_id else '-1234' end)-1 as 一到四月,
count(distinct case when (oper_date > =
to_date('2007-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and oper_date < =
to_date('2007-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) then split_id else '-1234' end)-1 as 五月
from v_tcm_user_prod a,
v_tcm_user b,
qh_twm_region d,
v_tpm_pack e
where e.PACK_ID in (‘1’,‘2’,‘3’)
and a.PACK_ID = e.PACK_ID
and a.bureau_no = '0013701'
and A.USER_ID = B.USER_ID
and B.REGION_NO = D.REGION_NO
and b.STATE = '1'
and b.USER_seq = '0'
and b.finish_flag = '1'
and a.state = '1'
and a.finish_flag = '1'
group by e.PACK_NAME, d.up_region_no