SQL语句查询，请帮忙！

[老杨_sz]

如表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

[dawugui]

没看懂你的规则.

[w75251455]

我懂了~~呵呵

[Hinco]

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]

结果
（所影响的行数为 16 行）

peono margin
----- ---------------------
0002 -100.0000
0003 -150.0000

（所影响的行数为 2 行）

[w75251455]

0001和0002看懂了~~可看不懂0003

[w75251455]

`_`!

[w75251455]

我怎么没想到.....昏迷....

[dnvodcwan]

同意 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

[little_elfin]

作为初级人士，我写了如下的ＳＱＬ：

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) 的ＳＱＬ后，发现我的思路和他相同，但他的ＳＱＬ
真是简明到了极点，佩服！　学习中．．．

[pumawang]

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

发现写法不太一样，基本都差不多啊。。