场景 : 做个抄表的报表. 原以为很简单.取得父节点下的各节点的合计就行了
//得到某组织层次下的子节点 select * from
C_ZZCC t where t.fjd = '00000001'
//得到所属单位为某个组织层次的用户 select *
from J_YHDA t where t.ssdw = '04000001'
//得到某用户的量费信息
select * from S_LF t where t.yhid = '040000000000PS'
//得到某用户当月抄见数-上月底数 select
t.dycjs - t.syds from S_LF t where t.yhid =
'040000000000PS'
//这个还有上面的不对. 这只是当月的.
//该做个判断.如果传进的参数是当月.就在s-lf里找,如果不是,就在history里找
//select t.dycjs
- t.syds from S_LF_HISTORY t
where t.yhid = '040000000000PS' and t.yf =
'201408'
//上面是某个用户在2014年8月.的抄表的多少
//201408月份.
父节点为00000001的抄表量
//
select sum (lf.dycjs - lf.syds),zzcc.mc
//
from s_lf_history lf ,c_zzcc zzcc
//
where lf.yf = '201408' and lf.yhid in
//
(
//
select yh.id from j_yhda yh where yh.ssdw = zzcc.id
//
)
//
and zzcc.id in
//
(
//
select id from c_zzcc where c_zzcc.fjd =
'00000001'
//
)
//
group by zzcc.mc
//yh.ssdw 应该 like zzcc.id% 不, 应该是. c_zzcc.fjd
like '00000001%'
// 不对.用% 的是qxm
//是按网络层次不是组织层次
v2
修改的太多.所有加了个版本
select lb.mc ,sum (lf.cjl)
,sum (lf.ysje)
from d_yhlb lb,j_pbxx pb,s_lf_history lf,c_dwlcc wlcc
where lf.yf = '201408'
and wlcc.id = 'wd07000239'
and pb.yhid = lf.yhid
and pb.pzwz = wlcc.id
and pb.yhlb = lb.id
group by lb.mc
//这个实现了.: 某个配装位置下的不同用电类别的 cjl 和ysje
select lb.mc ,sum (lf.cjl) ,sum (lf.ysje)
from d_yhlb lb,j_pbxx pb,s_lf_history lf,c_dwlcc wlcc
where lf.yf = '201408'
and wlcc.id = 'wd07000239'
and pb.yhid = lf.yhid
and pb.pzwz = wlcc.id
and pb.yhlb = lb.id
and
( lb.mc like '%路外%' or lb.mc like '%路内居民%'
or lb.mc like '%路内生产%' or lb.mc like
'%路内其他%'
)
group by lb.mc
// 选择这四种用电类型
*
*
select wlcc2.mc, sum(ydlbcjl), sum(ydlbysje) ,ydlb.mc
from c_dwlcc wlcc2,
(
select lb.mc mc ,sum (lf.cjl) ydlbcjl ,sum
(lf.ysje) ydlbysje,wlcc.qxm wlccqxm
from d_yhlb lb,j_pbxx pb,s_lf_history lf,c_dwlcc
wlcc
where lf.yf = '201408'
and pb.yhid = lf.yhid
and pb.pzwz = wlcc.id
and pb.yhlb = lb.id
and
( lb.mc like '%路外%' or lb.mc like '%路内居民%'
or lb.mc
like '%路内生产%' or lb.mc like '%路内其他%'
)
group by lb.mc,wlcc.qxm
) ydlb
where ydlb.wlccqxm like wlcc2.qxm || '%'
and wlcc2.fjd = 'wd07000001'
and wlcc2.mc like '%电业局%'
group by ydlb.mc,wlcc2.mc
// 按不同电业局不同名称 分开
select wlcc2.mc, sum(ydlbcjl), sum(ydlbysje) ,ydlb.mc
from c_dwlcc wlcc2,
(
select lb.mc mc ,sum (lf.cjl) ydlbcjl ,sum
(lf.ysje) ydlbysje,wlcc.qxm wlccqxm
from d_yhlb lb,j_pbxx pb,s_lf_history lf,c_dwlcc
wlcc
where lf.yf = '201408'
and pb.yhid = lf.yhid
and pb.pzwz = wlcc.id
and pb.yhlb = lb.id
and
( lb.mc like '%路外%' or lb.mc like '%路内居民%'
or lb.mc
like '%路内生产%' or lb.mc like '%路内其他%'
)
group by lb.mc,wlcc.qxm
) ydlb
where ydlb.wlccqxm like wlcc2.qxm || '%'
and wlcc2.fjd = 'wd07000001'
and wlcc2.mc like '%电业局%'
group by ydlb.mc,wlcc2.mc
order by wlcc2.mc
//groupby 有两个.最好orderby一下
*
点段的话不能看到所有电业局
(速度非常快)
select wlcc2.mc, sum(ydlbcjl), sum(ydlbysje),
ydlb.mc
from c_dwlcc wlcc2,
(select lb.mc mc,
sum(lf.sjbl) ydlbcjl,
sum(lf.ysje) ydlbysje,
wlcc.qxm wlccqxm
from d_yhlb lb, j_pbxx pb, s_lf_history lf, c_dwlcc wlcc
where lf.yf = '201408'
and pb.yhid = lf.yhid
and pb.pzwz = wlcc.id
and pb.yhlb = lb.id
and (lb.mc like '%路外%' or lb.mc like '%路内居民%' or
lb.mc like '%路内生产%' or lb.mc like '%路内其他%')
group by lb.mc, wlcc.qxm
) ydlb
where ydlb.wlccqxm like wlcc2.qxm || '%'
-- and( wlcc2.fjd =
'wd07000001' ) 只改了这一行
改成下面的
and wlcc2.qxm like ( select c.qxm|| '%'
from c_dwlcc c where c.id ='wd00000001' )
and wlcc2.mc like
'%电业局%'
group by ydlb.mc, wlcc2.mc
order by wlcc2.mc
//解决了上面的问题,速度慢了. 功能做好了
* cjl要改成sjbl
sjbl核算后才能对..所有本月的话没
*
..类别不是这么简单判断 的 还要修改.
select wlcc2.mc,
sum(ydlbcjl), sum(ydlbysje), ydlb.mc
from c_dwlcc
wlcc2,
(select lb.mc mc,
sum(lf.sjbl) ydlbcjl,
sum(lf.ysje) ydlbysje,
wlcc.qxm wlccqxm
from d_yhlb lb, j_pbxx pb, s_lf_history lf, c_dwlcc wlcc
where lf.yf = '201408'
and pb.yhid = lf.yhid
and pb.pzwz = wlcc.id
and pb.yhlb = lb.id
and (lb.mc like '%外%' or lb.mc like '%直供%' or lb.mc like
'%趸售%'
or lb.mc = '路内居民' or lb.mc = '路内生产' or lb.mc= '路内铁通1' or lb.mc =
'路内其他'
)
group by lb.mc, wlcc.qxm
) ydlb
where ydlb.wlccqxm like
wlcc2.qxm || '%'
-- and(
wlcc2.fjd = 'wd07000001' )
只改了这一行 改成下面的
and
wlcc2.qxm like ( select c.qxm|| '%' from c_dwlcc
c where c.id ='wd00000001' )
and wlcc2.mc like '%电业局%'
group by ydlb.mc,
wlcc2.mc
order by wlcc2.mc
路外的包括 前三个like的.
在js里做判断
最后的sql是用的李的一起类似的.改了一下
select mc id, yhlb, sum(dl) dl, sum(je) je
from (select (select mc from c_dwlcc c1 where
c1.qxm = t.qxm) mc,
(select mc from c_dwlcc c1 where c1.qxm = t.qxm) mc1,
yhlb, sum(dl) dl, sum(je) je
from (select qxm_xx.qxm, lf.dj, yhlb.mc
yhlb,
sum(lf.sjbl) dl,
sum(lf.ysje) je
from (select id, sjbl, ysje, dj, yf, yhid, ywzt
from s_lf_history) lf,
(select id, yhid, pzwz, yf, yhlb from j_pbxx_history) pbxx,
(select id, qxm, yf from c_dwlcc_history) wlcc,
(select id, ywzt, yf from j_yhda_history) yhda,
(select qxm, yf
from c_dwlcc