sql语句

create table tp_mmy_test_1
(
oper_date varchar2(20)
,oper_type varchar2(20)
,prod_name varchar2(100)
,order_cnt number
,succ_cnt number
)
;
select * from tp_mmy_test_1 for update
;
commit;
select distinct prod_name from tp_mmy_test_1;

create table tp_mmy_test_2
(
prod_name varchar2(100)
,cnt number
,reason_type varchar2(40)
,reason_desc varchar2(40)
,oper_date varchar2(20)
)
;

 

select a.oper_date
,a.prod_name
,a.order_cnt
,a.succ_cnt
,a.order_cnt - a.succ_cnt
,b.sys_re
,b.bus_re
from tp_mmy_test_1 a
left join (select prod_name
,sum(case when sb.reason_type = '系统原因' then nvl(sb.cnt, 0) end) sys_re -- 18649
,sum(case when sb.reason_type = '业务原因' then nvl(sb.cnt, 0) end) bus_re -- 51003
from tp_mmy_test_2 sb
group by prod_name
) b
on a.prod_name = b.prod_name
;
select * from tp_mmy_test_2;
select count(prod_name)
,count(distinct prod_name)
from tp_mmy_test_1
;