部署基建MIS，我估计至少是2个月的忙了，没有办法逃不过去，很多事情又要先放一放了！

视图一：v_contractApportionCount
SELECT category_name AS cname, COUNT(id) AS cid
FROM dbo.v_contractDetailInfo
WHERE (id IN
         (SELECT c.id
        FROM v_contractDetailInfo c LEFT OUTER JOIN
              apportion_line a ON a.contract_id = c.id
        GROUP BY c.id, c.total_value
        HAVING (SUM(isnull(a.value, 0.00)) = c.total_value)))
GROUP BY category_name

视图二：v_contractFinishCount
SELECT category_name AS cname, COUNT(id) AS cid
FROM dbo.v_contractDetailInfo
WHERE (id IN
         (SELECT c.id
        FROM v_contractDetailInfo c LEFT OUTER JOIN
              IVVOUCHER_DISASSEMBLE ivd ON ivd.contract_id = c.id
        GROUP BY c.id, c.total_value
        HAVING (SUM(isnull(ivd.lend_value, 0.00)) = c.total_value)))
GROUP BY category_name

视图三：v_contractBalanceCount
SELECT category_name AS cname, COUNT(id) AS cid
FROM dbo.v_contractDetailInfo cdi
WHERE (id IN
         (SELECT id
        FROM contract_balance_apply_bill AS cbab
        WHERE cbab.id = cdi.id))
GROUP BY category_name

视图四：v_contractPaymentCount
SELECT category_name AS cname, COUNT(id) AS cid
FROM dbo.v_contractDetailInfo
WHERE (id IN
         (SELECT c.id
        FROM v_contractDetailInfo c LEFT OUTER JOIN
              pmvoucher_disassemble pd ON pd.contract_id = c.id
        GROUP BY c.id, c.total_value
        HAVING (SUM(isnull(pd.value, 0.00)) = c.total_value)))
GROUP BY category_name

视图五：v_contractCount
SELECT c.category_name, COUNT(c.id) AS cid, ISNULL(cac.cid, 0) AS aid, COUNT(c.id)
     - ISNULL(cac.cid, 0) AS unaid, ISNULL(cbc.cid, 0) AS bid, COUNT(c.id)
     - ISNULL(cbc.cid, 0) AS unbid, ISNULL(cfc.cid, 0) AS fid, COUNT(c.id) - ISNULL(cfc.cid,
     0) AS unfid, ISNULL(cpc.cid, 0) AS pid, COUNT(c.id) - ISNULL(cpc.cid, 0)
     AS unpid
FROM dbo.v_contractDetailInfo c LEFT OUTER JOIN
     dbo.v_contractApportionCount cac ON
     c.category_name = cac.cname LEFT OUTER JOIN
     dbo.v_contractBalanceCount cbc ON
     c.category_name = cbc.cname LEFT OUTER JOIN
     dbo.v_contractFinishCount cfc ON c.category_name = cfc.cname LEFT OUTER JOIN
     dbo.v_contractPaymentCount cpc ON c.category_name = cpc.cname
GROUP BY c.category_name, cac.cid, cbc.cid, cfc.cid, cpc.cid

一：
现在要对：contract,code_contract和contract_balance_apply_bill表进行查询操作
条件是：在contract表里有一个contract_category_id字段对应的是code_contract表的id
       在contract_balance_apply_bill表中有一个contract_id字段队员的是contract表的id
达到的要求是：查询到code_contract里的每一条数据关联对应在
             contract_balance_apply_bill的数据条数
             
1）现在假设知道code_contract的其中一个id为221
  select count(id) from contract_balance_apply_bill where id in (select id from contract where contract_category_id=221)
  
2)现在假设不知道code_contract的字段
  select cc.name,count(cbab.id)
     from code_contract as cc,contract_balance_apply_bill  as cbab          
     where cbab.id in (select c.id from code_contract as cc1,contract as c where contract_category_id=cc1.id)
     group by cc.name
     
SELECT cc.name, COUNT(cbab.id) AS Expr1
FROM CODE_CONTRACT cc CROSS JOIN
     CONTRACT_BALANCE_APPLY_BILL cbab
WHERE (cbab.id IN
         (SELECT c.id
        FROM code_contract AS cc1, contract AS c
        WHERE contract_category_id = cc1.id))
GROUP BY cc.name      

现在加入v_contractDetailInfo视图
说明：v_contractDetailInfo里面包含的字段：category_name，contract.id，category_id

select category_name,count(cdi.id)
  from v_contractDetailInfo as cdi
  where cdi.id in(select id from contract_balance_apply_bill as cbab where cbab.id=cdi.id)
  group by category_name
  
SELECT category_name, COUNT(id) AS Expr1
FROM v_contractDetailInfo cdi
WHERE (id IN
         (SELECT id
        FROM contract_balance_apply_bill AS cbab
        WHERE cbab.id = cdi.id))
GROUP BY category_name

到此得到了所需要的结果

二：得到所以不在contract_balance_apply_bill的数据条数
select category_name,count(cdi.id)
  from v_contractDetailInfo as cdi
  where cdi.id not in(select id from contract_balance_apply_bill as cbab where cbab.id=cdi.id)
  group by category_name
  
SELECT category_name, COUNT(id) AS Expr1
FROM v_contractDetailInfo cdi
WHERE (id NOT IN
         (SELECT id
        FROM contract_balance_apply_bill AS cbab
        WHERE cbab.id = cdi.id))
GROUP BY category_name   

    要结合Spring和Hibernate，的确有很多的东西首先要健全起来。业务逻辑和商业逻辑的划分，DAO的自动生成，以及注射机制等等。。。
    今天问了一下这个系统的设计人员，他说 Cayenne的东西太具入侵性了，因为早期设计的时候没有想那么多所以才出现了诸多的影响系统性能的问题。本来我还打算借鉴一下他的设计结构的现在好重新的考虑一下了。

    这次测试基本上没有遇到什么大的问题，时间太长了，有些东西已经忘的差不多了，所以今后得时时的回头来看看，这次的收获有以下几点：熟悉了Ecllipse下的插件的使用，编写了build.xml使用了Debug等等功能；对配置Hibernate的环境有了进一步的熟悉，引入了Log4j。
    我想下一步的计划应该试着分离业务逻辑，和比较2个工具的特点，特别是tanghan的使用。