就是获得 详细信息, 总计 和小计 的导出 语句 select rownum ,row_number() over(partition by w.areaid order by w.areaid , w.winid )t_row,(CASE when GROUPING(rownum)= '1' and GROUPING(w.areaid)='1' then '总计' WHEN GROUPING(rownum)='1' THEN '小计' else w.areaid||'_'||w.winid END )dsdid ,sum( case w.fkqk when '0' then nvl(q.yxps,0) end )as qyxps, sum(w.yxps) wyxps,sum( nvl(q.yxps,0) + nvl(w.yxps,0)) sumyxps, sum(case w.fkqk when '0' then nvl(q.yjje,0.0) end) qyjje,sum(nvl(w.yjje,0)) wyjje, sum(case q.yjje when 0 then w.yjje else nvl(q.yjje,0)+nvl(w.yjje,0) end) sumyjje ,sum(case w.fkqk when '0' then 0 else nvl(q.yjje,0)+nvl(w.yjje,0) end) as paid, sum(case w.fkqk when '0' then nvl(q.yjje,0)+nvl(w.yjje,0) end)as poor from (select t_row,dsdid,ny,rq,zq,area,areaid,winid,qps,xsps,yxps, nvl(yjje,0.0) yjje,fkqk,bz,nvl(sp1,0) sp1,sp2,sp3,sp4, sp5,sp6,sp7,sp8,sp9,sp10 from ( select rownum t_row,b.dsdid,a.* from xt_dsd b left join ( select ny,rq,zq,area,areaid,winid,qps,xsps,yxps, nvl(yjje,0.0) yjje,fkqk,bz,nvl(sp1,0) sp1,sp2,sp3,sp4, sp5,sp6,sp7,sp8,sp9,sp10 from jfzd a )a on (b.area||b.winid)=(a.area||a.winid) where 1=1 <isNotEmpty prepend="AND" property="area"> a.area = #area# </isNotEmpty> )order by dsdid,winid) w left join (select t_row,dsdid,ny,rq,zq,area,areaid,winid,qps,xsps,yxps, nvl(yjje,0.0) yjje,fkqk ,bz,nvl(sp1,0) sp1,sp2,sp3,sp4, sp5,sp6,sp7,sp8,sp9,sp10 from ( select rownum t_row,b.dsdid,a.* from xt_dsd b left join( select ny,rq,zq,area,areaid,winid,qps,xsps,yxps, nvl(yjje,0.0) yjje,fkqk,bz,nvl(sp1,0) sp1,sp2,sp3,sp4, sp5,sp6,sp7,sp8,sp9,sp10 from jfzd a )a on (b.area||b.winid)=(a.area||a.winid)where 1=1 and a.area='GMK' )order by dsdid,winid)q on (w.dsdid=q.dsdid) group by grouping sets((w.areaid,rownum,w.winid),w.areaid ,'') order by w.areaid,w.winid 当用 <isNotEmpty prepend="AND" property="area"> a.area = #area# </isNotEmpty> 时就报 00600 错误 把sql 条件写死 and a.area='GMK' 就没问题
select rownum ,row_number() over(partition by w.areaid order by w.areaid , w.winid )t_row,(CASE when GROUPING(rownum)= '1'
and GROUPING(w.areaid)='1' then '总计' WHEN GROUPING(rownum)='1' THEN '小计'
else w.areaid||'_'||w.winid
END )dsdid ,sum( case w.fkqk when '0' then nvl(q.yxps,0) end )as qyxps,
sum(w.yxps) wyxps,sum( nvl(q.yxps,0) + nvl(w.yxps,0)) sumyxps,
sum(case w.fkqk when '0' then nvl(q.yjje,0.0) end) qyjje,sum(nvl(w.yjje,0)) wyjje,
sum(case q.yjje when 0 then w.yjje else nvl(q.yjje,0)+nvl(w.yjje,0) end) sumyjje ,sum(case w.fkqk when '0'
then 0 else nvl(q.yjje,0)+nvl(w.yjje,0) end) as paid,
sum(case w.fkqk when '0' then nvl(q.yjje,0)+nvl(w.yjje,0) end)as poor
from
(select t_row,dsdid,ny,rq,zq,area,areaid,winid,qps,xsps,yxps,
nvl(yjje,0.0) yjje,fkqk,bz,nvl(sp1,0) sp1,sp2,sp3,sp4,
sp5,sp6,sp7,sp8,sp9,sp10 from (
select rownum t_row,b.dsdid,a.* from xt_dsd b
left join (
select ny,rq,zq,area,areaid,winid,qps,xsps,yxps,
nvl(yjje,0.0) yjje,fkqk,bz,nvl(sp1,0) sp1,sp2,sp3,sp4,
sp5,sp6,sp7,sp8,sp9,sp10 from jfzd a
)a on (b.area||b.winid)=(a.area||a.winid)
where 1=1
<isNotEmpty prepend="AND" property="area">
a.area = #area#
</isNotEmpty>
)order by dsdid,winid) w
left join
(select t_row,dsdid,ny,rq,zq,area,areaid,winid,qps,xsps,yxps,
nvl(yjje,0.0) yjje,fkqk ,bz,nvl(sp1,0) sp1,sp2,sp3,sp4,
sp5,sp6,sp7,sp8,sp9,sp10 from (
select rownum t_row,b.dsdid,a.* from xt_dsd b
left join(
select ny,rq,zq,area,areaid,winid,qps,xsps,yxps,
nvl(yjje,0.0) yjje,fkqk,bz,nvl(sp1,0) sp1,sp2,sp3,sp4,
sp5,sp6,sp7,sp8,sp9,sp10 from jfzd a
)a on (b.area||b.winid)=(a.area||a.winid)where 1=1
and a.area='GMK'
)order by dsdid,winid)q on (w.dsdid=q.dsdid)
group by grouping sets((w.areaid,rownum,w.winid),w.areaid ,'') order by w.areaid,w.winid 当用
<isNotEmpty prepend="AND" property="area">
a.area = #area#
</isNotEmpty>
时就报 00600 错误
把sql 条件写死 and a.area='GMK' 就没问题