实际工作用到的：同店换房时计算老房间费用

需求：
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 ContractCalendarwhere ContractID = 'R150040218'and BizDate>='2016-10-25'and InputBillTag = 1order by BizDate descselect 83.870968*31 + 86.666667*4*/declare @ContractID varchar(50)declare @contractroomid varchar(50)declare @date dateset @ContractID = 'R150038016'set @contractroomid = 'R150038016C001' Select @date = Convert(char(10),getdate(),126)  --老房间押金select Deposit from ContractRoomwhere ContractID = @ContractID--计算老房间剩余房费和服务费select SUM(DayRentPrice) sumDayRent,SUM(DayServiceFee) sumDayService,ContractID from ContractCalendarwhere ContractID = @ContractIDand 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 #temp1from     (        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    ) aselect 电费,冷水费,热水费 from #temp1select @el1 = 电费 from #temp1select @wc1 = 冷水费 from #temp1select @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)) UsedAmountinto #temp2from    (      select           StoreID          ,ContractRoomID          ,RoomNo          ,AccountSubjectCode          ,MAX(SequenceID)MaxSequenceID          ,MIN(SequenceID)MinSequenceID      from CounterLog      where  contractroomid=@contractroomid      group by StoreID,ContractRoomID,RoomNo,AccountSubjectCode      ) Eleleft join CounterLog Cl on ele.ContractRoomID=cl.ContractRoomID      and ele.RoomNo=cl.RoomNo      and ele.AccountSubjectCode=cl.AccountSubjectCode      and ele.MaxSequenceID=cl.SequenceIDleft join CounterLog Cl2on ele.ContractRoomID=Cl2.ContractRoomID      and ele.RoomNo=Cl2.RoomNo      and ele.AccountSubjectCode=Cl2.AccountSubjectCode      and ele.MinSequenceID=Cl2.SequenceIDleft join contractfeeconfig cffon ele.ContractRoomID=cff.ContractID+'C001'      and ele.AccountSubjectCode=cff.AccountSubjectCodeleft join      (select ContractRoomID,isnull(sum(Amount),0) Amount        from BillDetail        where AccountSubjectcode in('10501','10502','10503','10504')        group by ContractRoomID      ) bbon ele.ContractRoomID=bb.ContractRoomIDselect * from #temp2declare @el2 decimal(10,2)declare @wc2 decimal(10,2)declare @wh2 decimal(10,2)select @el2 = usedamount from #temp2where AccountSubjectCode = '10501'select @wc2 = usedamount from #temp2where AccountSubjectCode = '10502'select @wh2 = usedamount from #temp2where AccountSubjectCode = '10503'--比较计算出来的水电读数与页面展示数据是否一致if @el1 = @el2    print '电费计算正确'else     print '电费计算错误'if @wc1 = @wc2    print '冷水费计算正确'else     print '冷水费计算错误'if @wh1 = @wh2    print '热水费计算正确'else     print '热水费计算错误'drop table #temp1drop table #temp2

主要用到的sql就是多表关联。正所谓孰能生巧，万变不离其宗！需要在实际工作中多使用，多连汇贯通！