租金计划表：leas_rent_scheme
leas_rent_scheme.writeoff_state：租金核销状态（1—未核销，2—部分核销，3—完全核销）
租金实收表：leas_rent_income
根据leas_rent_income.rent_scheme_id可以到leas_rent_scheme找到相应的rent_scheme_id，
通过比较leas_rent_income.actual_charge和leas_rent_scheme.scheme_charge_sum的大小判断leas_rent_scheme.writeoff_state是否正确。谢谢各位大虾。

不懂

select b.writeoff_state,(case when a.actual_charge<b.scheme_charge_sum  then '2' else (case when a.actual_charge=b.scheme_charge_sum then 3 else 1 end )  end) as writeoff_state
from leas_rent_incom a left join leas_rent_scheme  b   on a.leas_rent_income.rent_scheme_id=leas_rent_scheme.rent_scheme_id
如果查出来的两个栏位不一样,说明就是不正确的

[ 本帖最后由 sky_yang_sky 于 2010-3-31 15:31 编辑 ]

select c.*
from(select a.rent_scheme_id,a.scheme_charge_sum,b.actual_charge_sum,a.writeoff_state,
                                  (case when a.scheme_charge_sum>b.actual_charge_sum then'2'
                                        when a.scheme_charge_sum<b.actual_charge_sum then'???'
                                        when a.scheme_charge_sum=b.actual_charge_sum then'3'
                                        else'1'
                                   end)actual_writeoff_state
from leas_rent_scheme a
left outer join
(select t.rent_scheme_id,sum(t.actual_charge)actual_charge_sum
 from leas_rent_income t
 group by t.rent_scheme_id)b
on a.rent_scheme_id=b.rent_scheme_id
 where a.his_flag!=1)c
where c.writeoff_state!=c.actual_writeoff_state