create or replace view v_ht_basebudget4fee_mk as
select
/******************************************************************************
NAME: TIA
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2010 周凌 NOTES: 概算的 每一明细级
的 四个费用项 合计
包括 初始的 和 实际进度和出库得
******************************************************************************/
A.id,
A.itemcode,A.itemname,-- 建筑
sum
(
case when B.feetypeid like '1.2.2%' then
B.passfee
else
0
end
) JZ_sumpassfee ,sum
(
case when C.feetypeid like '1.2.2%' then
C.FMONEY
else
0
end
) JZ_sumFMONEY ,
-- 安装
sum
(
case when B.feetypeid like '1.2.4%' then
B.passfee
else
0
end
) AZ_sumpassfee ,sum
(
case when C.feetypeid like '1.2.4%' then
C.FMONEY
else
0
end
) AZ_sumFMONEY ,-- 设 备
sum
(
case when b.feetypeid like '1.2.3%' then
b.passfee
else
0
end
) SB_sumpassfee ,sum
(
case when C.feetypeid like '1.2.3%' then
C.FMONEY
else
0
end
) SB_sumFMONEY ,-- 其 他
sum
(
case when B.feetypeid like '1.2.5%' then
B.passfee
else
0
end
) QT_sumpassfee ,sum
(
case when C.feetypeid like '1.2.5%' then
C.FMONEY
else
0
end
) QT_sumFMONEY ,
-- 矿建
sum
(
case when B.feetypeid like '1.2.1%' then
B.passfee
else
0
end
) KJ_sumpassfee ,sum
(
case when C.feetypeid like '1.2.1%' then
C.FMONEY
else
0
end
) KJ_sumFMONEY
from
(select * from HT_BUDGETITEM where to_number(id)>910 ) A,
HT_BGTFEEINI B ,-- 实际进度和出库方向 以及 归集方向 合并
(
select
T2.BUDGETID,
T2.FEETYPEID ,
T1.FILEFEE FMONEY,T2.sno
from
BID_FTEMPOSUB T1,
HT_DTBGTFEETO T2
where T1.TOID = T2.sno(+)
union all
-- 出库
select
T2.budgetid,
T2.feetypeid,
-- 动产 不含税
case when T2.ISRASSET ='1' then nvl(T1.out_money,0)
-- 不动产 含含税 的金额
when T2.ISRASSET ='2' then Nvl(T1.out_money,0) + nvl(T1.outratem,0)
end outfee ,
T1.sno
from
WZ_MATOUT_SUB T1 ,
(
select
T2.ISRASSET,
T1.* from
WZ_MATOUT T1,
HT_BUDGETITEM T2
where T1.BUDGETID = T2.ITEMCODE(+)
) T2
where
T1.pid = T2.INSTANCEID(+)
union all
--归集
select
dgtcode ,FEETYPEID,amount ,sno
from PA_COSTABTIONDTL
union all
--分摊
select BUDGETID,FEETYPEID,DIVIDFEE,sno from
HT_DIVIDERUN_SUB
) C
where
A.itemcode = b.BUDGETID(+) AND
A.itemcode = C.BUDGETID(+) group by A.id,A.itemcode,A.itemname
请问这个oracle视图里面的有sum函数请问高手像这样的能否详解一下什么意思啊sum
(
case when B.feetypeid like '1.2.1%' then
B.passfee
else
0
end
) KJ_sumpassfee ,
select
/******************************************************************************
NAME: TIA
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2010 周凌 NOTES: 概算的 每一明细级
的 四个费用项 合计
包括 初始的 和 实际进度和出库得
******************************************************************************/
A.id,
A.itemcode,A.itemname,-- 建筑
sum
(
case when B.feetypeid like '1.2.2%' then
B.passfee
else
0
end
) JZ_sumpassfee ,sum
(
case when C.feetypeid like '1.2.2%' then
C.FMONEY
else
0
end
) JZ_sumFMONEY ,
-- 安装
sum
(
case when B.feetypeid like '1.2.4%' then
B.passfee
else
0
end
) AZ_sumpassfee ,sum
(
case when C.feetypeid like '1.2.4%' then
C.FMONEY
else
0
end
) AZ_sumFMONEY ,-- 设 备
sum
(
case when b.feetypeid like '1.2.3%' then
b.passfee
else
0
end
) SB_sumpassfee ,sum
(
case when C.feetypeid like '1.2.3%' then
C.FMONEY
else
0
end
) SB_sumFMONEY ,-- 其 他
sum
(
case when B.feetypeid like '1.2.5%' then
B.passfee
else
0
end
) QT_sumpassfee ,sum
(
case when C.feetypeid like '1.2.5%' then
C.FMONEY
else
0
end
) QT_sumFMONEY ,
-- 矿建
sum
(
case when B.feetypeid like '1.2.1%' then
B.passfee
else
0
end
) KJ_sumpassfee ,sum
(
case when C.feetypeid like '1.2.1%' then
C.FMONEY
else
0
end
) KJ_sumFMONEY
from
(select * from HT_BUDGETITEM where to_number(id)>910 ) A,
HT_BGTFEEINI B ,-- 实际进度和出库方向 以及 归集方向 合并
(
select
T2.BUDGETID,
T2.FEETYPEID ,
T1.FILEFEE FMONEY,T2.sno
from
BID_FTEMPOSUB T1,
HT_DTBGTFEETO T2
where T1.TOID = T2.sno(+)
union all
-- 出库
select
T2.budgetid,
T2.feetypeid,
-- 动产 不含税
case when T2.ISRASSET ='1' then nvl(T1.out_money,0)
-- 不动产 含含税 的金额
when T2.ISRASSET ='2' then Nvl(T1.out_money,0) + nvl(T1.outratem,0)
end outfee ,
T1.sno
from
WZ_MATOUT_SUB T1 ,
(
select
T2.ISRASSET,
T1.* from
WZ_MATOUT T1,
HT_BUDGETITEM T2
where T1.BUDGETID = T2.ITEMCODE(+)
) T2
where
T1.pid = T2.INSTANCEID(+)
union all
--归集
select
dgtcode ,FEETYPEID,amount ,sno
from PA_COSTABTIONDTL
union all
--分摊
select BUDGETID,FEETYPEID,DIVIDFEE,sno from
HT_DIVIDERUN_SUB
) C
where
A.itemcode = b.BUDGETID(+) AND
A.itemcode = C.BUDGETID(+) group by A.id,A.itemcode,A.itemname
请问这个oracle视图里面的有sum函数请问高手像这样的能否详解一下什么意思啊sum
(
case when B.feetypeid like '1.2.1%' then
B.passfee
else
0
end
) KJ_sumpassfee ,
sum
(
case when B.feetypeid like '1.2.1%' then
B.passfee
else
0
end
) KJ_sumpassfee ,如果字段B.feetypeid 满足 like '1.2.1%'时,则KJ_sumpassfee 的值是字段B.passfee值的和,也就是SUM(B.passfee);
否则,KJ_sumpassfee 的值就是0