| 大神们，我语句库里运行没问题，可添加模板数据集里，预览可以，可扩展字段不行 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 |
