select a.STAT_TIME,
b.F_CITY_CODE as CITY_CODE,
sum(a.pay_fee) as AMOUNT,
d.fee as BANK_AMOUNT
from brpt_bf_n_8035_temp_t a,
om_area_t b,
(select to_char(op_day, 'yyyymmdd'), city_code, sum(t.fee3_crm) fee
from rpt_user.brpt_reserve_deposit_month_t@bill2crm t, om_area_t c
where c.F_CITY_CODE = t.city_code
and c.f_area_level = '3'
and t.op_day >= to_date('2014101', 'yyyymmdd')
AND t.op_day < to_date('20141002', 'yyyymmdd')
group by op_day, city_code) d
where a.city_code = b.f_area_code
and b.F_CITY_CODE = d.city_code
and b.f_area_level = '3'
and a.STAT_TIME = '20141001'
group by STAT_TIME, b.F_CITY_CODE, d.fee
order by b.F_CITY_CODE;
b.F_CITY_CODE as CITY_CODE,
sum(a.pay_fee) as AMOUNT,
d.fee as BANK_AMOUNT
from brpt_bf_n_8035_temp_t a,
om_area_t b,
(select to_char(op_day, 'yyyymmdd'), city_code, sum(t.fee3_crm) fee
from rpt_user.brpt_reserve_deposit_month_t@bill2crm t, om_area_t c
where c.F_CITY_CODE = t.city_code
and c.f_area_level = '3'
and t.op_day >= to_date('2014101', 'yyyymmdd')
AND t.op_day < to_date('20141002', 'yyyymmdd')
group by op_day, city_code) d
where a.city_code = b.f_area_code
and b.F_CITY_CODE = d.city_code
and b.f_area_level = '3'
and a.STAT_TIME = '20141001'
group by STAT_TIME, b.F_CITY_CODE, d.fee
order by b.F_CITY_CODE;
解决方案 »
- plsql9以上版本和8有啥区别?
- IPV6问题
- 将记录分类别放在不同的txt
- Oracle8i 升级到11g的相关问题
- select count(*) into int_int from PAN_IntegralSpoilSet i where i.activitycid=
- start with connect by的问题
- 建表问题
- 请大家帮忙,求一条更新语句。我的是这样写的,谁还有最好的!
- 一个关于oracle enterprise manager consol的问题
- SYSTEM表空间底下常用的表
- 重建数据库时如何修改字符集(oracle 11g)
- Oracle 如果安装时重用存在的数据库目录
则select子句中只能是group by 子句中的字段,或是能通过这些字段生成的公式,或是组函数,或是常数
d.fee as BANK_AMOUNT在group子句中没有,因此在这里写会报错可以改为max(d.fee) as BANK_AMOUNT
b.F_CITY_CODE as CITY_CODE,
sum(a.pay_fee)as AMOUNT,
d.fee as BANK_AMOUNT
group by STAT_TIME, ( b.F_CITY_CODE)
select a.STAT_TIME,
b.F_CITY_CODE as CITY_CODE,
sum(a.pay_fee) over (partition by STAT_TIME, b.F_CITY_CODE) as AMOUNT,
d.fee as BANK_AMOUNT
from brpt_bf_n_8035_temp_t a,
om_area_t b,
(select to_char(op_day, 'yyyymmdd'), city_code, sum(t.fee3_crm) fee
from rpt_user.brpt_reserve_deposit_month_t@bill2crm t, om_area_t c
where c.F_CITY_CODE = t.city_code
and c.f_area_level = '3'
and t.op_day >= to_date('2014101', 'yyyymmdd')
AND t.op_day < to_date('20141002', 'yyyymmdd')
group by op_day, city_code) d
where a.city_code = b.f_area_code
and b.F_CITY_CODE = d.city_code
and b.f_area_level = '3'
and a.STAT_TIME = '20141001'
-- 不需要 group by 了 ,我们出的是明细。
order by b.F_CITY_CODE;