两张表,项目表和子项表,子项属于项目,一个项目对应多个子项,没有外键约束,但子项表记录了项目的编号可以通过这个编号找到对应。
子项还有对应的合同表,也是一对多的关系,首先合同表group by子项编号取出没个子项的总合同额,子项表left join把总合同额带着。然后和项目表union已项目编号为parentID,项目的总合同额就是其下所有叶子子项的合同额的汇总。
prj_subprj 子项表,prj_prj项目表,agree子项合同表
SELECT
sp.subprjno AS BH,
sp.subprjname AS MC,
ag.amount AS HT,
sp.prjno AS PARENT
FROM
(
SELECT
ps.subprjno,
ps.subprjname,
ps.prjno
FROM
prj_subprj ps
) sp
LEFT JOIN (
SELECT
A .subprjno,
SUM (A .amount) AS amount
FROM
agree A
GROUP BY
A .subprjno
) ag ON sp.subprjno = ag.subprjno
UNION
SELECT
pp.prjno AS BH,
pp.prjname AS MC,
NULL AS HT,
NULL AS PARENT
FROM
prj_prj pp
项目的总合同额如何从叶子节点的子项合同额汇总过来,创建这个视图