insert into water
select '0001','2004-10-05',100 union all
select '0001','2004-11-05',150 union all
select '0001','2004-12-05',220 union all
select '0002','2004-10-05',100 union all
select '0002','2004-11-05',110 union all
select '0002','2004-12-05',200
select userid,watertime,本月应交 from (
select c.userid,d.watertime,c.watervol as 本月应交 from water c join (select userid,min(watertime) as watertime from water group by userid) d
on c.userid=d.userid and c.watertime=d.watertime
union all
select b.userid,b.watertime,(b.watervol-a.watervol) as benyue from water b,water a
where b.userid=a.userid and datediff(m,b.watertime,a.watertime)=-1
) a
order by watertime,userid
----------------------------------------
userid watertime 本月应交
0001 2004-10-05 100.00
0002 2004-10-05 100.00
0001 2004-11-05 50.00
0002 2004-11-05 10.00
0001 2004-12-05 70.00
0002 2004-12-05 90.00