我们的购销合同的总金额由以下金额构成:货物的总金额,而货物的总金额由货物本身的金额+附件金额构成,也就是说,我们想要获取购销合同的总金额,就要获取合同下的所有货物的总金额(货物总金额要获取所有货物的金额,以及这个货物下的所有附件的总金额)。
听起来有点绕,但是我们把它写成sql语句,分好层次,我们就能够清晰的理解购销合同的总金额是如何来的了:
1)可以通过货物的新增时,同步计算货物的总金额和和合同的总金额;在附件新增时,同步计算附件的总金额和合同的总金额;(程序来完成)(修改、删除时重新计算)
2)SQL查询实现
货物的总金额
- select sum(cnumber*price) as cptotal from contract_product_c
- where contract_id ='471e562b-bfa5-4ba7-a2b5-17e9b0d40179'
附件的总金额
- select sum(cnumber*price) as exttotal from ext_cproduct_c
- where contract_product_id in (select contract_product_id from contract_product_c where contract_id=c.contract_id)
最终
- select
- (select count(*) from contract_product_c
- where contract_id=c.contract_id) as cpnum,
- (select count(*) from ext_cproduct_c
- where contract_product_id in (select contract_product_id from contract_product_c where contract_id=c.contract_id)
- ) as extnum,
- (
- nvl((select sum(cnumber*price) as cptotal from contract_product_c
- where contract_id =c.contract_id),0)
- +
- nvl(
- (select sum(cnumber*price) as exttotal from ext_cproduct_c
- where contract_product_id in (select contract_product_id from contract_product_c where contract_id=c.contract_id))
- ,0)
- ) as total_amount,
- c.CONTRACT_ID,c.OFFEROR,c.CONTRACT_NO,c.SIGNING_DATE,c.INPUT_BY,c.CHECK_BY,c.INSPECTOR,c.IMPORT_NUM,c.CREQUEST,c.CUSTOM_NAME,c.DELIVERY_PERIOD,c.SHIP_TIME,c.TRADE_TERMS,c.REMARK,c.PRINT_STYLE,c.OLD_STATE,c.STATE,c.OUT_STATE
- from contract_c c
我们修改ContractMapper的find配置语句块:
- <!-- 查询多个 -->
- <!-- 如果支持异构数据,必须针对每个数据库写一个SQL语句,因为使用了底层数据函数nvl -->
- <select id="find" parameterType="map" resultMap="contractRM">
- select
- (select count(*) from contract_product_c
- where contract_id=c.contract_id) as cpnum,
- (select count(*) from ext_cproduct_c
- where contract_product_id in (select contract_product_id from
- contract_product_c where contract_id=c.contract_id)
- ) as extnum,
- ( nvl((select sum(cnumber*price) as cptotal from contract_product_c
- where contract_id =c.contract_id),0)
- +
- nvl((select sum(cnumber*price) as exttotal from ext_cproduct_c
- where contract_product_id in (select contract_product_id from
- contract_product_c where contract_id=c.contract_id)),0)
- ) as total_amount,
- c.CONTRACT_ID,c.OFFEROR,c.CONTRACT_NO,c.SIGNING_DATE,c.INPUT_BY,c.CHECK_BY,
- c.INSPECTOR,c.IMPORT_NUM,c.CREQUEST,c.CUSTOM_NAME,c.DELIVERY_PERIOD,c.SHIP_TIME,c.
- TRADE_TERMS,c.REMARK,c.PRINT_STYLE,c.OLD_STATE,c.STATE,c.OUT_STATE
- from contract_c c
- </select>
接下来我们重启服务器来测试:
我们可以看到,这个购销合同的总结是1600

我们来检验一下是不是1600。
首先,它有两个货物

总金额为:200+1200=1400。然后看一下附件,只有一件附件
附件总金额为200,所以最终总金额为1400+200=1600,和我们程序计算的一模一样。我们的总金额计算方法成功!
但是,我们要在增加、删除以及修改的时候对总金额进行更新,所以要修改一部分代码,这里我们就不在赘述
最后
以上就是哭泣可乐最近收集整理的关于【springmvc+mybatis项目实战】杰信商贸-21.合同总金额SQL的全部内容,更多相关【springmvc+mybatis项目实战】杰信商贸-21内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复