1.数据字典

1.1一般它由编号+名称构成。

性别：0101男，0102女
包装单位：0201 PCS，0202 SETS
区县：02901西安市 02902宝鸡市
通用的数据字典，它可以存放多个分类，结构必须是ID+NAME
TYPE+ID+NAME

1.2数据字典都在哪里使用？

单选的组合；下拉框

2.表的业务后缀

_c 业务表 customer
_b 基础表 base
_p 权限表 privilege

3.级联删除

Mybatis多层关联关系时，级联删除必须一级一级的删除。多级时要专门写mapper方法。
在mybatis在service，删除货物时，先调用附件的service删除方法
通过sql，高速删除（批量删除）每层都要实现一个删除

业务：要求直接显示合同下的货物数和附件数
select count(*) from contract_product_c
where contract_id=’928eb2ae-23ba-46e0-9ad0-054354f66af8’

select count(*) from ext_cproduct_c
where contract_product_id in (select contract_product_id from contract_product_c where contract_id=’928eb2ae-23ba-46e0-9ad0-054354f66af8’)

3.1获取货物数和附件数

c.*

from contract_c c

3.2 PO、VO、BO有什么区别？

PO 持久化对象，一般就直接对象数据库表
VO 视图对象，一般对应页面jsp
BO 业务对象，一般对应复杂业务

4.合同总金额业务【sql的强大】

4.1 货物的总金额

select cnumber*price as cptotal from contract_product_c
where contract_id =’471e562b-bfa5-4ba7-a2b5-17e9b0d40179’

4.2 附件的总金额

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)

4.3最终

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(cnumberprice) as cptotal from contract_product_c
where contract_id =c.contract_id),0)
+
nvl(
(select sum(cnumberprice) 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

5.业务：购销合同查看【sql的强大】

查看合同的主信息，查看合同下的货物信息，还要查看附件信息，货物和附件的信息要显示出它们的关联关系。
利用面对对象的关联关系来实现上面的需求非常简单。
合同、货物、附件、生产厂家构建复杂多级关联的SQL时原则
1）挑选最小的结果集
2）滚雪球（左连接），逐步往上加内容
3）重复字段，需要起别名（mybatis）

！在实际业务中几乎没有内连接的情况，直接就用左连接即可。

最终实现业务的超长sql

select 
c.contract_id,c.offeror,c.contract_no,c.signing_date,c.input_by,c.check_by,c.inspector,c.total_amount,c.import_n    um,c.crequest,c.custom_name,c.delivery_period,c.ship_time,c.trade_terms,c.remark,c.print_style,c.old_state,c.sta    te,c.out_state,c.create_by,c.create_dept,c.create_time, 
t.contract_product_id,t.product_no,t.product_image,t.product_desc,t.cnumber,t.out_number,t.loading_rate,t.box_num,t.packing_unit,t.price,t.amount,t.finished,t.exts,t.order_no,t.factory_id,t.full_name,t.factory_name,t.contacts,t.phone, t.ext_cproduct_id,     t.ctype,t.ext_product_no,t.ext_product_image,t.ext_product_desc,t.ext_cnumber,t.ext_packing_         unit,t.ext_price,t.ext_amount,t.product_request,
 t.ext_order_no,
 t.ext_factory_id,t.ext_full_name,t.ext_factory_name,t.ext_contacts,t.ext_phone

from
(
  select 
      contract_id,offeror,contract_no,signing_date,input_by,check_by,inspector,total_amount,import_num,crequest,custom_name,delivery_period,ship_time,trade_terms,remark,print_style,old_state,state,out_state,create_by,create_dept,create_time
   from contract_c ) c left join (
   select
          cp.contract_product_id,cp.contract_id,
          cp.product_no,cp.product_image,cp.product_desc,cp.cnumber,cp.out_number,cp.loading_rate,cp.box_num,cp.packing_unit,cp.price,cp.amount,cp.finished,cp.exts,cp.order_no,
          cp.factory_id,cp.full_name,cp.factory_name,cp.contacts,cp.phone,

          ext.ext_cproduct_id,
          ext.ctype,ext.product_no as ext_product_no,ext.product_image as ext_product_image,ext.product_desc as ext_product_desc,
          ext.cnumber as ext_cnumber,ext.packing_unit as ext_packing_unit,ext.price as ext_price,ext.amount as ext_amount,
          ext.product_request,ext.order_no as ext_order_no,
          ext.factory_id as ext_factory_id,ext.full_name as ext_full_name,ext.factory_name as ext_factory_name,ext.contacts as ext_contacts,ext.phone as ext_phone

    from
    (
        select
          cp.contract_product_id,cp.contract_id,
          cp.product_no,cp.product_image,cp.product_desc,cp.cnumber,cp.out_number,cp.loading_rate,cp.box_num,cp.packing_unit,cp.price,cp.amount,cp.finished,cp.exts,cp.order_no,
          f.factory_id,f.full_name,f.factory_name,f.contacts,f.phone
        from
        (select 
        contract_product_id,contract_id,factory_id,
        product_no,product_image,product_desc,cnumber,out_number,loading_rate,box_num,packing_unit,price,amount,finished,exts,order_no
         from contract_product_c) cp
        left join 
        (select factory_id,full_name,factory_name,contacts,phone from factory_c) f
        on cp.factory_id=f.factory_id
    ) cp
    left join
    (
        select
          ext.ext_cproduct_id,ext.contract_product_id,
          ext.ctype,ext.product_no,ext.product_image,ext.product_desc,ext.cnumber,ext.packing_unit,ext.price,ext.amount,ext.product_request,ext.order_no,
          f.factory_id,f.full_name,f.factory_name,f.contacts,f.phone
        from
        (
        select 
            ext_cproduct_id,contract_product_id,factory_id,
            ctype,product_no,product_image,product_desc,cnumber,packing_unit,price,amount,product_request,order_no
         from ext_cproduct_c 
        ) ext
        left join 
        (select factory_id,full_name,factory_name,contacts,phone from factory_c) f
        on ext.factory_id=f.factory_id
    ) ext
    on cp.contract_product_id=ext.contract_product_id) t on c.contract_id=t.contract_id where c.contract_id=#{contractId}