需要统计出以下数值四张表结构主要字段A (bureau_no,USER_ID,pack_id,SPLIT_ID,TIME)
B (bureau_no,USER_ID,CUST_ID,REGION_NO)
C (BUREAU_NO,CUST_ID,cust_tren_type)
D (REGION_NO,up_REGION_NO)我要取这样的数字BUREAU_NO 为区域,up_REGION_NO 为次级区域,REGION_NO 为最小区域BUREAU_NO >up_REGION_NO>REGION_NOA.USER_ID=B.USER_ID
B.CUST_ID=C.CUST_ID
B.REGION_NO=D.REGION_NO
1)所统计的数字为不同区域(BUREAU_NO)不同时间(TIME)区间的 COUNT (SPLIT_ID)2) 取出的数据表结构大概为
区域 TIME1 TIME2
--------------------------------------------------------------------
如果BUREAU_NO='1'|
则分up_REGION_NO COUNT (SPLIT_ID) COUNT (SPLIT_ID)
--------------------------------------------------------------------
取C.cust_tren_type='1'
且BUREAU_NO='1' 时 COUNT (SPLIT_ID) COUNT (SPLIT_ID) ---------------------------------------------------------------------
按BUREAU_NO 取
COUNT (SPLIT_ID) COUNT (SPLIT_ID)----------------------------------------------------------------------如何罗列到一张报表里 同时取出来 万分感谢
B (bureau_no,USER_ID,CUST_ID,REGION_NO)
C (BUREAU_NO,CUST_ID,cust_tren_type)
D (REGION_NO,up_REGION_NO)我要取这样的数字BUREAU_NO 为区域,up_REGION_NO 为次级区域,REGION_NO 为最小区域BUREAU_NO >up_REGION_NO>REGION_NOA.USER_ID=B.USER_ID
B.CUST_ID=C.CUST_ID
B.REGION_NO=D.REGION_NO
1)所统计的数字为不同区域(BUREAU_NO)不同时间(TIME)区间的 COUNT (SPLIT_ID)2) 取出的数据表结构大概为
区域 TIME1 TIME2
--------------------------------------------------------------------
如果BUREAU_NO='1'|
则分up_REGION_NO COUNT (SPLIT_ID) COUNT (SPLIT_ID)
--------------------------------------------------------------------
取C.cust_tren_type='1'
且BUREAU_NO='1' 时 COUNT (SPLIT_ID) COUNT (SPLIT_ID) ---------------------------------------------------------------------
按BUREAU_NO 取
COUNT (SPLIT_ID) COUNT (SPLIT_ID)----------------------------------------------------------------------如何罗列到一张报表里 同时取出来 万分感谢
解决方案 »
- oracle 11gR2 rac 升级报错
- 弱弱的问一下 oracle能不能在查询出来的同时,对查询结果做计算?
- start with connect by性能问题
- 求1条查询语句
- 《Oracle9i实务管理讲座--系统核心篇》这本书大家觉得怎么样?
- 如何插入日期型数据
- 为什么Oracle816 的dbms_output.put_line只能输出255 bytes per line?
- 第一次以论坛形式庆祝自已生日
- oracle中设置缺省值用Sysdate?
- oracle数据库迁移后遇到的问题
- 高分求简单问题>>sqlplus能登陆,但plsql却不能登陆.
- 20分请教一个很简单的问题~在线等。着急·!
select D.up_REGION_NO,sum(case when A.TIME> xxx and A.TIME <XXX then 1 else 0 end) as TIME1,sum(case when A.TIME> xxx and A.TIME <XXX then 1 else 0 end) as TIME2
from A,B,C,D
where A.USER_ID=B.USER_ID and B.CUST_ID=C.CUST_ID and B.REGION_NO=D.REGION_NO
and A.BUREAU_NO='1' and C.cust_tren_type<>'1'
group by D.up_REGION_NO
union all
select D.REGION_NO,sum(case when A.TIME> xxx and A.TIME <XXX then 1 else 0 end) as TIME1,sum(case when A.TIME> xxx and A.TIME <XXX then 1 else 0 end) as TIME2
from A,B,C,D
where A.USER_ID=B.USER_ID and B.CUST_ID=C.CUST_ID and B.REGION_NO=D.REGION_NO
and A.BUREAU_NO='1' and C.cust_tren_type='1'
group by D.REGION_NO
union all
select A.BUREAU_NO,sum(case when A.TIME> xxx and A.TIME <XXX then 1 else 0 end) as TIME1,sum(case when A.TIME> xxx and A.TIME <XXX then 1 else 0 end) as TIME2
from A
where A.BUREAU_NO<>'1'
group by A.BUREAU_NO用3个sql连起来
sum(case when A.TIME > xxx and A.TIME <XXX then 1 else 0 end) as TIME1,sum(case when A.TIME > xxx and A.TIME <XXX then 1 else 0 end这里我要取的数据为 distinct (SPLIT_ID) 该怎么办
select D.up_REGION_NO,count(distinct a1.SPLIT_ID) as TIME1,count(distinct a2.SPLIT_ID) as TIME2
from (select * from A where BUREAU_NO='1' and TIME> xxx and TIME <XXX) a1,
(select * from A where BUREAU_NO='1' and TIME> xxx2 and TIME <XXX2) a2,B,C,D
where a1.USER_ID=B.USER_ID and a2.USER_ID=B.USER_ID and B.CUST_ID=C.CUST_ID and B.REGION_NO=D.REGION_NO
and C.cust_tren_type<>'1'
group by D.up_REGION_NO我把第1部分写了,其它的你看这改,应该问题不大了!
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取不出数字
如果把第二个时间区间放的比第一个大,可以取出来,但是 一到四月 和 5月的数字是一样的。如果两个时间区间不同 就纯粹取不出数字 . 郁闷那
把
and a1.state = '1'
and a1.finish_flag = '1'
and a2.state = '1'
and a2.finish_flag = '1'
这些条件放到from后面,这样
(select *
from v_tcm_user_prod
where bureau_no = '0013701' and state = '1' and finish_flag = '1'
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 state = '1' and finish_flag = '1'
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,
再不出数据的话,我就无能为力了,
实在不行就写1个时间