首页 新闻 搜索 专区 学院

oracle group by 问题

0
悬赏园豆:10 [待解决问题]

select count(distinct b.id) as recover,count(distinct d.reid) as workCount,a.name,a.service_no,
c.money_recovery as recoverTotal,c.workcount as workTotal
from dgrpmanagermsg a,ep_owerecord_info b,ep_dict_manage c,ep_market_record d where a.service_no in(
select login_no from ep_sys_loginmsg_info where group_id='10070'
) and a.name=c.region_name and a.service_no=c.region_no and c.status_code='04'
and b.service_name=a.name and b.service_no=a.service_no and b.record_status='04'
and replace(substr(b.op_time,length(b.op_time)-10,7),'-','') = substr(c.order_no,length(c.order_no)-7,6 )
and d.manager_name=a.name and d.login_no=a.service_no and d.status='3'
and replace(substr(d.create_time,0,7),'/','') = substr(c.order_no,length(c.order_no)-7,6 )
and substr(c.order_no,length(c.order_no)-7,6 ) = to_char(sysdate,'yyyyMM')
group by a.name,a.service_no,c.money_recovery,c.workcount

 

 

 

 

and b.service_name=a.name and b.service_no=a.service_no and b.record_status='04'
and replace(substr(b.op_time,length(b.op_time)-10,7),'-','') = substr(c.order_no,length(c.order_no)-7,6 )

b表在这种条件下结果集为空,导致最终的结果集为空。其他表的结果集不为空的,有什么解决办法吗?

永远爱妮的主页 永远爱妮 | 初学一级 | 园豆:192
提问于:2017-06-16 15:40
< >
分享
所有回答(2)
0

使用左连接试试 左边表不为空 将b表放后面

SAI2014 | 园豆:239 (菜鸟二级) | 2017-06-16 15:46
1

服了,inner join当然b表没数据,结果集当然没数据啊,用left join啊

柿子Boy | 园豆:256 (菜鸟二级) | 2017-06-16 16:34
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册