表结构如下
用户基本表: UserInfo: userid,username,cityid,typeid
城市列表: CityInfo: cityid,cityname
用户类型表: TypeInfo: typeid,typename,
年金标准: RepriseStand: year,cityid,typeid,reprisestand
收费记录: ReceiveInfo: receiveid,year,userid,reprise
优惠表: FreeInfo: year,userid,freereprise
用户基本表: UserInfo: userid,username,cityid,typeid
城市列表: CityInfo: cityid,cityname
用户类型表: TypeInfo: typeid,typename,
年金标准: RepriseStand: year,cityid,typeid,reprisestand
收费记录: ReceiveInfo: receiveid,year,userid,reprise
优惠表: FreeInfo: year,userid,freereprise
freereprise,reprisesum,balance
case when c.reprisesum is null then 0.00
else c.reprisesum
end as reprisesum
case when e.freereprise is null then 0.00
else e.freereprise end as freereprise
case when c.reprisesum is null and e.freereprise is null
then reprisestand
when c.reprisesum is null and e.freereprise is not null
then reprisestand-e.freereprise
when c.reprisesum is not null and e.freereprise is null
then reprisestand-c.reprisesum
else reprisestand-c.reprisesum-e.freereprise
end as balance
from UserInfo a
inner join RepriseStand b on a.cityid=b.cityid and a.typeid=b.cityid
left join (select year,userid,sum(reprise) as reprisesum from Receiveinfo
group by year,userid) c on a.userid=c.userid and b.year=c.year
left join FreeInfo d on a.userid=d.userid and b.year=d.year
left join CityInfo e on a.cityid=e.cityid
left join TypeInfo f on a.TypeInfo=f.TypeInfo请前辈们多多指教!