select (select shortname from ldcom where comcode = v.comcode) || '____', (select codename
from ldcode
where codetype = 'salechnl'
and code = v.code),
sum(cbcount),
nvl(sum(cbsumprem), 0),
sum(wtcount),
nvl(sum(wtsumprem), 0),
sum(ctcount),
nvl(sum(ctsumprem), 0),
sum(xtcount),
nvl(sum(xtsumprem), 0)
from (select k.comcode comcode,
k.shortname,
d.code CODE,
d.codename,
(select count(distinct l.contno)
from v_temp_bqcont l
where l.optype = 'CB'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) cbcount,
(select SUM(l.money)
from v_temp_bqcont l
where l.optype = 'CB'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) cbsumprem,
(select count(DISTINCT l.contno)
from v_temp_bqcont l
where l.optype = 'WT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) wtcount,
-1 * (select sum(l.money)
from v_temp_bqcont l
where l.optype = 'WT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) wtsumprem,
(select count(DISTINCT l.contno)
from v_temp_bqcont l
where l.optype = 'CT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) ctcount,
-1 * (select sum(l.money)
from v_temp_bqcont l
where l.optype = 'CT'
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) ctsumprem,
(select COUNT(DISTINCT l.contno)
from v_temp_bqcont l
where l.optype IN ('XT', 'YT')
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) xtcount,
-1 * (select sum(l.money)
from v_temp_bqcont l
where l.optype IN ('XT', 'YT')
and l.managecom like k.comcode || '%'
and opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
and l.salechnl = D.CODE) xtsumprem
from ldcom k, ldcode d
where d.codetype = 'salechnl'
and k.comcode not like '8600%'
and k.comcode like '86030201%'
and d.code = '03'
and LENGTH(k.comcode) = 8
order by k.comcode, d.code) v
group by ROLLUP(v.comcode, v.code)
解决方案 »
- oracle导出某用户下的所有表的数据结构、视图名称、函数名称、存储过程名称、对应表空间
- wrap命令是在哪里执行的啊,在SQLPLUS还是操作系统的CMD还是哪里
- sql语句树形结构怎么写
- 关于接口表短时间频繁的查询,删除问题.
- 牛人指点一下新人oracle基础的东西
- 创建了分区表,分区已经建好了,,但是发现建好的表无法在增加分区,怎么回事?
- (windows oracle)+(solaris oracle)复制的问题!!!!
- JOB 的问题,肯请大家来帮帮我
- 急救:oracle8i监听无法启动
- execute immediate动态执行SQL,SQL字符串的长度是不是有限制?
- oracle安装
- oracle 11g 在不区分大小写的情况下,求出邮件地址中包含姓的员工信息。其中邮件以首字母大写的形式返回 怎么弄?
select l.optype,l.managecom,l.salechnl,COUNT(DISTINCT l.contno) cc,sum(l.money) mm
from v_temp_bqcont l
where opdate between to_date('2017-01-01','yyyy-mm-dd') and to_date('2017-07-27','yyyy-mm-dd')
group by l.optype,l.managecom,l.salechnl
第二层:将上述中间表与ldcom k, ldcode 关联按条件取值;
第三层:按v.comcode, v.code分组得最终结果另:中间层中尽量不要order by,这会增加额外开销
把查询列中的子查询使用关联查询按条件取值,
把你子查询上的order by去掉,节省开销,而且还是没意义的开销