需求： 1. 租房时，房间由于其他原因，需要换房 2. 老房间的费用需要进行结算：2016-11-15开始换房，老房间2017-02-20房间到期，房费缴纳到2016-12-19 3. 换房时，需要先退还2016-11-15~2016-12-19日的房费/服务费，并对已用的水电费进行结算
主要用到的思想： 1. 先将水电费手动计算并插入到临时表1中； 2. 然后查询页面上所展示出来的数据并插入到临时表2中； 3. 进行对比结果并判断结果； 4. 丢弃临时表。
如下：
/* select ContractID,DayRentPrice,DayServiceFee,* from ContractCalendar where ContractID = 'R150040218' and BizDate>='2016-10-25' and InputBillTag = 1 order by BizDate desc select 83.870968*31 + 86.666667*4 */ declare @ContractID varchar(50) declare @contractroomid varchar(50) declare @date date set @ContractID = 'R150038016' set @contractroomid = 'R150038016C001' Select @date = Convert(char(10),getdate(),126) --老房间押金 select Deposit from ContractRoom where ContractID = @ContractID --计算老房间剩余房费和服务费 select SUM(DayRentPrice) sumDayRent,SUM(DayServiceFee) sumDayService,ContractID from ContractCalendar where ContractID = @ContractID and BizDate>=@date --换房日 and InputBillTag = 1 group by ContractID --手动计算水电费用 declare @E numeric(14,5) declare @CW numeric(14,5) declare @HW numeric(14,5) SET @E= (select StoreFeePrice from ContractFeeConfig where ContractID = @ContractID and AccountSubjectCode = '10501') SET @CW=(select StoreFeePrice from ContractFeeConfig where ContractID = @ContractID and AccountSubjectCode = '10502') SET @HW=(select StoreFeePrice from ContractFeeConfig where ContractID = @ContractID and AccountSubjectCode = '10503') --select convert (numeric(14,5) --,coalesce(storefeeprice,0)) as price,* from ContractFeeConfig --where ContractID = 'R160002722' and AccountSubjectCode in ('10501','10502','10503') declare @el1 decimal(10,2) declare @wc1 decimal(10,2) declare @wh1 decimal(10,2) select cast(电表使用量*@E as decimal(10,2)) as '电费', cast(冷水使用量*@CW as decimal(10,2)) as '冷水费', cast(热水使用量*@HW as decimal(10,2)) as '热水费' into #temp1 from ( select top 1 ElectricReading as '电初始读数',ColdWaterReading as '冷水初始读数',HotWaterReading as '热水初始读数', ElectricReadingNew as '电最新读数',ColdWaterReadingNew as '冷水最新读数',HotWaterReadingNew as '热水最新读数' ,(ElectricReadingNew-ElectricReading) as '电表使用量',(ColdWaterReadingNew-ColdWaterReading) as '冷水使用量' ,(HotWaterReadingNew-HotWaterReading) as '热水使用量' from Bill b inner join ContractFeeConfig cfc on cfc.ContractID = b.ContractID where b.ContractID = @ContractID ) a select 电费,冷水费,热水费 from #temp1 select @el1 = 电费 from #temp1 select @wc1 = 冷水费 from #temp1 select @wh1 = 热水费 from #temp1 --页面显示的水电费用 select ele.ContractRoomID ,ele.RoomNo ,isnull(bb.Amount,0) Amount ,ele.AccountSubjectCode ,cl2.CounterReading InitReading ,cl.CounterReading LastReading ,cast(cl2.CounterReading as varchar(10))+'到'+cast(cl.CounterReading as varchar(10)) Remark ,(cl.CounterReading-cl2.CounterReading) UsedReading ,cast((cl.CounterReading-cl2.CounterReading)*isnull(cff.StoreFeePrice,0) as decimal(10,2)) UsedAmount into #temp2 from ( select StoreID ,ContractRoomID ,RoomNo ,AccountSubjectCode ,MAX(SequenceID)MaxSequenceID ,MIN(SequenceID)MinSequenceID from CounterLog where contractroomid=@contractroomid group by StoreID,ContractRoomID,RoomNo,AccountSubjectCode ) Ele left join CounterLog Cl on ele.ContractRoomID=cl.ContractRoomID and ele.RoomNo=cl.RoomNo and ele.AccountSubjectCode=cl.AccountSubjectCode and ele.MaxSequenceID=cl.SequenceID left join CounterLog Cl2 on ele.ContractRoomID=Cl2.ContractRoomID and ele.RoomNo=Cl2.RoomNo and ele.AccountSubjectCode=Cl2.AccountSubjectCode and ele.MinSequenceID=Cl2.SequenceID left join contractfeeconfig cff on ele.ContractRoomID=cff.ContractID+'C001' and ele.AccountSubjectCode=cff.AccountSubjectCode left join (select ContractRoomID,isnull(sum(Amount),0) Amount from BillDetail where AccountSubjectcode in('10501','10502','10503','10504') group by ContractRoomID ) bb on ele.ContractRoomID=bb.ContractRoomID select * from #temp2 declare @el2 decimal(10,2) declare @wc2 decimal(10,2) declare @wh2 decimal(10,2) select @el2 = usedamount from #temp2 where AccountSubjectCode = '10501' select @wc2 = usedamount from #temp2 where AccountSubjectCode = '10502' select @wh2 = usedamount from #temp2 where AccountSubjectCode = '10503' --比较计算出来的水电读数与页面展示数据是否一致 if @el1 = @el2 print '电费计算正确' else print '电费计算错误' if @wc1 = @wc2 print '冷水费计算正确' else print '冷水费计算错误' if @wh1 = @wh2 print '热水费计算正确' else print '热水费计算错误' drop table #temp1 drop table #temp2主要用到的sql就是多表关联。正所谓孰能生巧，万变不离其宗！需要在实际工作中多使用，多连汇贯通！
