A表统计各宿舍住宿人员名单,包括宿舍编号(NUMBER)、人员编号(NUM)、入住日期(INDATE)等;
B表统计各宿舍水电费用,包括宿舍编号(NUMBER)、超额费用(没超为0,有的宿舍超额使用就须缴超额费) select NUMBER,NUM,sum(datediff(dd,INDATE,getdate())) as indays into #temp from TA
group by NUMBER,NUMselect a.NUMBER,a.NUM,(cast(b.超额费用 as float)/totalindays)*a.indays from #temp a
inner join (select NUMBER,sum(indays) as totalindays from #temp group by NUMBER) T
on a.NUMBER=b.NUMBER
A表统计各宿舍住宿人员名单,包括宿舍编号(NUMBER)、人员编号(NUM)、入住日期(INDATE)等;
B表统计各宿舍水电费用,包括宿舍编号(NUMBER)、超额费用(没超为0,有的宿舍超额使用就须缴超额费) select NUMBER,NUM,sum(datediff(dd,INDATE,getdate())) as indays into #temp from TA
group by NUMBER,NUMselect a.NUMBER,a.NUM,(cast(b.超额费用 as float)/totalindays)*a.indays from #temp a
inner join (select NUMBER,sum(indays) as totalindays from #temp group by NUMBER) T
on a.NUMBER=b.NUMBER
A表统计各宿舍住宿人员名单,包括宿舍编号(NUMBER)、人员编号(NUM)、入住日期(INDATE)等;
B表统计各宿舍水电费用,包括宿舍编号(NUMBER)、超额费用(没超为0,有的宿舍超额使用就须缴超额费) select NUMBER,NUM,sum(datediff(dd,INDATE,getdate())) as indays into #temp from TA
group by NUMBER,NUMselect a.NUMBER,a.NUM,(cast(b.超额费用 as float)/totalindays)*a.indays from #temp a
inner join (select NUMBER,sum(indays) as totalindays from #temp group by NUMBER) T
on a.NUMBER=b.NUMBER
A表统计各宿舍住宿人员名单,包括宿舍编号(NUMBER)、人员编号(NUM)、入住日期(INDATE)等;
B表统计各宿舍水电费用,包括宿舍编号(NUMBER)、超额费用(没超为0,有的宿舍超额使用就须缴超额费) select NUMBER,NUM,sum(datediff(dd,INDATE,getdate())) as indays into #temp from TA
group by NUMBER,NUMselect a.NUMBER,a.NUM,(cast(b.超额费用 as float)/totalindays)*a.indays from #temp a
inner join (select NUMBER,sum(indays) as totalindays from #temp group by NUMBER) T
on a.NUMBER=b.NUMBER