select
area_no,
city_no,
sum(CDR_NUM ) CDR_NUM,
tele_type
from (
select a.*,area_code
from (
select
t.area_no,
t.city_no,
sum(CDR_NUM ) CDR_NUM,
t.tele_type
FROM (
SELECT *
FROM report.report_sczb_gc_user_call a
where substr(acct_month,1,4)=acct_year
and acct_month<= ?
and city_no in (?)
) t,
dmcode.dmcode_city b
WHERE t.area_no=b.area_no and t.CITY_no = B.CITY_no
group by
t.tele_type,
t.area_no,
t.city_no
)a,
dim.dim_area_no b
where a.area_no= b.area_no
)
group by area_no,
city_no,
tele_type,
area_code
order by area_code
area_no,
city_no,
sum(CDR_NUM ) CDR_NUM,
tele_type
from (
select a.*,area_code
from (
select
t.area_no,
t.city_no,
sum(CDR_NUM ) CDR_NUM,
t.tele_type
FROM (
SELECT *
FROM report.report_sczb_gc_user_call a
where substr(acct_month,1,4)=acct_year
and acct_month<= ?
and city_no in (?)
) t,
dmcode.dmcode_city b
WHERE t.area_no=b.area_no and t.CITY_no = B.CITY_no
group by
t.tele_type,
t.area_no,
t.city_no
)a,
dim.dim_area_no b
where a.area_no= b.area_no
)
group by area_no,
city_no,
tele_type,
area_code
order by area_code
解决方案 »
- sql server 2008 移植到 oracle 10g
- 请教个SQL递归求和的问题
- oracle存储过程的事务问题
- linux下如何将写好的存储过程(txt格式的文件)导入oracle?
- 求一统计问题,在线等!
- oracle里面,action调用ibatis里面我想传一个不是form的参数值,sql如何写啊?
- 用scott/tiger进入sqlplus,说用户锁定
- 请问我想让SQL Server与Oracle中的指定表的指定字段保持同步,是否可行?应该如何做?
- 问,Oracle可不可在建表时为某个字段设置默认值?
- 安装Oracle 8i (Release 3 (8.1.7) ) 的问题
- AS4 下安装 oracle 9i 时的问题,求高手帮忙解决!在线等
- create table 急!!!!!!!!!!!!!1111
A.AREA_NO,
A.CITY_NO,
SUM(CDR_NUM) CDR_NUM,
A.TELE_TYPE
FROM REPORT.REPORT_SCZB_GC_USER_CALL A
JOIN DMCODE.DMCODE_CITY B
ON A.AREA_NO = B.AREA_NO AND A.CITY_NO = B.CITY_NO
JOIN DIM.DIM_AREA_NO C ON A.AREA_NO = C.AREA_NO
WHERE SUBSTR(a.ACCT_MONTH, 1, 4) = a.ACCT_YEAR
AND a.ACCT_MONTH <= ?
AND a.CITY_NO IN (?)
GROUP BY C.AREA_CODE,A.CITY_NO,A.TELE_TYPE,A.AREA_NO
ORDER BY AREA_CODE
A.AREA_NO,
A.CITY_NO,
A.TELE_TYPE,
SUM(CDR_NUM)
OVER(PARTITION BY C.AREA_CODE,a.CITY_NO,a.TELE_TYPE,AREA_NO
ORDER BY a.ACCT_MONTH
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CDR_NUM,
FROM REPORT.REPORT_SCZB_GC_USER_CALL A
JOIN DMCODE.DMCODE_CITY B
ON A.AREA_NO = B.AREA_NO AND A.CITY_NO = B.CITY_NO
JOIN DIM.DIM_AREA_NO C ON A.AREA_NO = C.AREA_NO
WHERE SUBSTR(a.ACCT_MONTH, 1, 4) = a.ACCT_YEAR
AND a.ACCT_MONTH <= ?
AND a.CITY_NO IN (?)
GROUP BY C.AREA_CODE,A.CITY_NO,A.TELE_TYPE,A.AREA_NO
ORDER BY AREA_CODE
我也没有说我给出的sql运行无错.
我给出的只是解决问题的思路.
你连需求都没完全描述清楚,我凭什么给你解答正确?
我给的sql有问题,那你总得提供一个可以给我测试的表创建脚本和样本数据呀
下面的代码修改了一下,我仍然不能保证正确
SELECT C.AREA_CODE,
A.AREA_NO,
A.CITY_NO,
A.TELE_TYPE,
a.ACCT_MONTH,
SUM(CDR_NUM)
OVER(PARTITION BY C.AREA_CODE,a.CITY_NO,a.TELE_TYPE,AREA_NO
ORDER BY a.ACCT_MONTH
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CDR_NUM,
FROM REPORT.REPORT_SCZB_GC_USER_CALL A
JOIN DMCODE.DMCODE_CITY B
ON A.AREA_NO = B.AREA_NO AND A.CITY_NO = B.CITY_NO
JOIN DIM.DIM_AREA_NO C ON A.AREA_NO = C.AREA_NO
WHERE SUBSTR(a.ACCT_MONTH, 1, 4) = a.ACCT_YEAR
AND a.ACCT_MONTH <= ?
AND a.CITY_NO IN (?)
GROUP BY C.AREA_CODE,A.ACCT_MONTH,A.CITY_NO,A.TELE_TYPE,A.AREA_NO
ORDER BY AREA_CODE,ACCT_MONTH
(
ACCT_MONTH VARCHAR2(6),
ACCT_YEAR VARCHAR2(4),
AREA_NO VARCHAR2(10),
TELE_TYPE VARCHAR2(10),
CDR_NUM NUMBER,
CALL_DURATION NUMBER,
TOTAL_JF_TIMES NUMBER
)编制单位:中国联通集团
项目 单位 本期值 本年累计
总通话次数 次 91398254.
总通实际话时长 秒 8269167426.
总计费时长 分钟 188488213.
2618368 11 这里的21 和11代表的是网络类型21是C网11是G网,而且都是一月份的数据,我要G网的年累计值和这里11的本期值一样;。