SQL语句查询，请帮忙！
如表A中查询出丢失存款金额流水行的工号。
表A:
peono date consumemoney fundmoney surplusmoney
0002 2007-01-01 09:59:33.000 0.00 100.00 100.00
0001 2007-01-01 10:59:33.000 0.00 100.00 100.00
0003 2007-01-01 13:59:33.000 0.00 100.00 100.00
0003 2007-01-02 08:59:33.000 8.00 0.00 92.00
0001 2007-01-02 11:23:33.000 2.00 0.00 98.00
0002 2007-01-02 16:59:33.000 2.00 0.00 98.00
0003 2007-01-03 11:59:33.000 2.00 0.00 140.00
0001 2007-01-03 14:59:33.000 5.00 0.00 93.00
0002 2007-01-03 18:59:33.000 4.00 0.00 194.00
0002 2007-01-04 07:59:33.000 2.00 0.00 192.00
0001 2007-01-04 08:59:33.000 2.00 0.00 91.00
0003 2007-01-04 12:59:33.000 3.00 0.00 137.00
0002 2007-01-05 13:59:33.000 4.00 0.00 188.00
0001 2007-01-05 17:59:33.000 2.00 0.00 89.00
0003 2007-01-05 19:59:33.000 4.00 0.00 233.00
0003 2007-01-06 08:59:33.000 20.00 0.00 213.00
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
peono margin（差额） <0(比如0002，100.00－2.00－4.00－2.00－4.00－188.00＝-100)
－－－－－－－－－－－－－－－－－
0002 -100
0003 -150
[解决办法]
create table A(peono varchar(4),[date] datetime,consumemoney money,
fundmoney money,surplusmoney money)
insert A select '0002 ', '2007-01-01 09:59:33 ', 0.00, 100.00, 100.00
union all select '0001 ', '2007-01-01 10:59:33 ', 0.00, 100.00, 100.00
union all select '0003 ', '2007-01-01 13:59:33 ', 0.00, 100.00, 100.00
union all select '0003 ', '2007-01-02 08:59:33 ', 8.00, 0.00, 92.00
union all select '0001 ', '2007-01-02 11:23:33 ', 2.00, 0.00, 98.00
union all select '0002 ', '2007-01-02 16:59:33 ', 2.00, 0.00, 98.00
union all select '0003 ', '2007-01-03 11:59:33 ', 2.00, 0.00, 140.00
union all select '0001 ', '2007-01-03 14:59:33 ', 5.00, 0.00, 93.00
union all select '0002 ', '2007-01-03 18:59:33 ', 4.00, 0.00, 194.00
union all select '0002 ', '2007-01-04 07:59:33 ', 2.00, 0.00, 192.00
union all select '0001 ', '2007-01-04 08:59:33 ', 2.00, 0.00, 91.00
union all select '0003 ', '2007-01-04 12:59:33 ', 3.00, 0.00, 137.00
union all select '0002 ', '2007-01-05 13:59:33 ', 4.00, 0.00, 188.00
union all select '0001 ', '2007-01-05 17:59:33 ', 2.00, 0.00, 89.00
union all select '0003 ', '2007-01-05 19:59:33 ', 4.00, 0.00, 233.00
union all select '0003 ', '2007-01-06 08:59:33 ', 20.00, 0.00, 213.00
select * from (select peono,sum(fundmoney)-sum(consumemoney)-
(select top 1 surplusmoney from A where peono=t.peono order by [date] desc) as margin
from A t group by peono) tt where margin <> 0
drop table A
[解决办法]
同意 hinco(桃色德鲁依)
不過我覺得下面這樣更直觀
select * from
(select peono,[Margin]=sum(fundmoney)-sum(consumemoney)- (select top 1 surplusmoney from A where peono=t.peono order by [date] desc)
from a t
group by peono )m
where m.margin <> 0
[解决办法]
作为初级人士，我写了如下的ＳＱＬ：
select * from (select distinct peono from Money ) allPeono
select * from ( select allSummoney.peono, (allSummoney.leftmoney - totalmoney.surplusmoney) as margin from
(select peono, (sum(fundmoney)-sum(consumemoney)) as leftmoney from Money
group by peono ) allSummoney
left join
(select a.peono,a.surplusmoney from money a,(select peono,max(date) as date from Money group by peono ) b where
a.peono= b.peono and a.date=b.date) totalmoney
on allSummoney.peono = totalmoney.peono ) c
where margin <0
最后，可以完成作者的任务．
然后运行和研究dnvodcwan(jc.franck) 的ＳＱＬ后，发现我的思路和他相同，但他的ＳＱＬ
真是简明到了极点，佩服！ 学习中．．．
[解决办法]
select peono,sum(margin) margin From
(select peono,A1.consumemoney,A1.surplusmoney,date , (select top 1surplusmoney From A where peono=A1.peono and date <A1.Date order by Date desc)- A1.consumemoney-A1.surplusmoney margin
From A A1 ) A3
where margin <> 0
Group by peono
发现写法不太一样，基本都差不多啊。。
