大神们，我语句库里运行没问题，可添加模板数据集里，预览可以，可扩展字段不行
select plm1,je1,plm3,rs3,je3 from (select plm1, je1, plm3, sum(rs3) rs3, sum(je3) je3
from (select hy_pp1. plm1,
hy_pp1.je1,
hy_pp2.hykh2,
hy_pp3.plm3,
hy_pp3.rs3,
hy_pp3.je3
from (select substr(cdlcatid, 0, 2) plm1,
sum(cdlcjje) je1,
count(distinct CDLCNO) rs1
from cardxflog
where trunc(cdldate) >= to_date('${sdate}', 'YYYY-MM-DD')
and trunc(cdldate) <
to_date('${edate}', 'YYYY-MM-DD') + 1
and cdlmkt in ('${md}')
and cdlcatid not in '0'
and substr(cdlcatid, 0, 2) = '${lb}'
group by substr(cdlcatid, 0, 2)) hy_pp1
left join (select distinct (cdlcno) hykh2,
substr(cdlcatid, 0, 2) plm2
from cardxflog
where trunc(cdldate) >=
to_date('${sdate}', 'YYYY-MM-DD')
and trunc(cdldate) <
to_date('${edate}', 'YYYY-MM-DD') + 1
and cdlmkt in ('${md}')
and cdlcatid not in '0'
/* and substr(cdlcatid, 0, 2) = '01'*/
order by substr(cdlcatid, 0, 2)) hy_pp2
on hy_pp1.plm1 = hy_pp2.plm2
right join (select count(distinct CDLCNO) rs3,
cdlcno hykh3,
substr(cdlcatid, 0, 2) plm3,
sum(cdlcjje) je3
from cardxflog
where trunc(cdldate) >=
to_date('${sdate}', 'YYYY-MM-DD')
and trunc(cdldate) <
to_date('${edate}', 'YYYY-MM-DD') + 1
and cdlmkt in ('${md}')
and cdlcatid not in '0'
/*and substr(cdlcatid, 0, 2) = '01'*/
group by cdlcno, substr(cdlcatid, 0, 2)) hy_pp3
on hy_pp2. hykh2 = hy_pp3. hykh3
group by hy_pp1. plm1,
hy_pp1.je1,
hy_pp2.hykh2,
hy_pp3.plm3,
hy_pp3.rs3,
hy_pp3.je3
having hy_pp2.hykh2 is not null)
group by plm1, je1, plm3)
where je3 not in '0'
order by je3 desc
