select dtEcit=convert(varchar(10),dtEdit,120),
mReceiptProxyGathering=(select sum(mReceiptProxyGathering) from GoodsReceipt where convert(varchar(10),dtEdit,120)<=convert(varchar(10),a.dtEdit,120))
from GoodsReceipt a --結果
dtEcit mReceiptProxyGathering
----------------------------------------------
2004-11-09 1200.0000
2004-11-10 2200.0000
2004-11-11 3400.0000
mReceiptProxyGathering=(select sum(mReceiptProxyGathering) from GoodsReceipt where convert(varchar(10),dtEdit,120)<=convert(varchar(10),a.dtEdit,120))
from GoodsReceipt a --結果
dtEcit mReceiptProxyGathering
----------------------------------------------
2004-11-09 1200.0000
2004-11-10 2200.0000
2004-11-11 3400.0000
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000152' , .0000 , .0000 , 1000.0000 , '2004-11-11 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000157' , .0000 , .0000 , 1200.0000 , '2004-11-11 18:01:46.327' , 16 )
得出的结果是:2004-11-11 2200.0000
2004-11-11 2200.0000
我这里没环境,没测试:
select convert(varchar(10),a.dtEdit,120) as dtEcit,sum(a.mReceiptProxyGathering) as mReceiptProxyGathering
from GoodsReceipt a left join GoodsPayout b
on a.vcSheetNo = b.vcSheetNo where (b.vcSheetNo is null)
group by convert(varchar(10),a.dtEdit,120)
INSERT [GoodsPayout] ( [vcSheetNo] , [mPayoutGoodsMoney] , [dtEdit] , [nEditor] ) VALUES ( '0000000152' , 1000.0000 , '2004-11-5 20:10:54.077' , 14 )
INSERT [GoodsPayout] ( [vcSheetNo] , [mPayoutGoodsMoney] , [dtEdit] , [nEditor] ) VALUES ( '0000000157' , 1200.0000 , '2004-11-11 20:10:54.077' , 14 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000150' , .0000 , .0000 , 1000.0000 , '2004-11-11 18:01:46.327' , 16 )收款表中有以下数据
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000150' , .0000 , .0000 , 1000.0000 , '2004-11-1 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000151' , .0000 , .0000 , 1200.0000 , '2004-11-2 18:01:46.327' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000152' , .0000 , .0000 , 1000.0000 , '2004-11-3 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000153' , .0000 , .0000 , 1200.0000 , '2004-11-4 18:01:46.327' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000154' , .0000 , .0000 , 1000.0000 , '2004-11-5 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000157' , .0000 , .0000 , 1200.0000 , '2004-11-11 18:01:46.327' , 16 )
则统计出来的数据是:日期 未付款
…..
2004-11-1 1000
2004-11-2 2200
2004-11-3 3200
2004-11-4 4400
2004-11-5 4400
2004-11-11 3400根据单号来判断某个单子是否已付款(vcsheetno),可以这样来判断哪些单子没有付款:
Select vcsheetno from Goodsreceipt where vcSheetno not in (select vcsheetno from goodspayout)
不好意思,可能没说明白,向付款表中插几条测试数据:
INSERT [GoodsPayout] ( [vcSheetNo] , [mPayoutGoodsMoney] , [dtEdit] , [nEditor] ) VALUES ( '0000000152' , 1000.0000 , '2004-11-5 20:10:54.077' , 14 )
INSERT [GoodsPayout] ( [vcSheetNo] , [mPayoutGoodsMoney] , [dtEdit] , [nEditor] ) VALUES ( '0000000157' , 1200.0000 , '2004-11-11 20:10:54.077' , 14 )
INSERT [GoodsPayout] ( [vcSheetNo] , [mPayoutGoodsMoney] , [dtEdit] , [nEditor] ) VALUES ( '0000000150' , 1000.0000 , '2004-11-11 20:10:54.077' , 14 )收款表中有以下数据
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000150' , .0000 , .0000 , 1000.0000 , '2004-11-1 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000151' , .0000 , .0000 , 1200.0000 , '2004-11-2 18:01:46.327' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000152' , .0000 , .0000 , 1000.0000 , '2004-11-3 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000153' , .0000 , .0000 , 1200.0000 , '2004-11-4 18:01:46.327' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000154' , .0000 , .0000 , 1000.0000 , '2004-11-5 18:10:51.890' , 16 )
INSERT [GoodsReceipt] ( [vcSheetNo] , [mReceiptGoodsMoney] , [mReceiptProxyCarrage] , [mReceiptProxyGathering] , [dtEdit] , [nEditor] ) VALUES ( '0000000157' , .0000 , .0000 , 1200.0000 , '2004-11-11 18:01:46.327' , 16 )
则统计出来的数据是:日期 未付款
…..
2004-11-1 1000
2004-11-2 2200
2004-11-3 3200
2004-11-4 4400
2004-11-5 4400
2004-11-11 3400根据单号来判断某个单子是否已付款(vcsheetno),可以这样来判断哪些单子没有付款:
Select vcsheetno from Goodsreceipt where vcSheetno not in (select vcsheetno from goodspayout)
(select dtEdit=convert(varchar(10),dtEdit,120),mReceiptProxyGathering=sum(mReceiptProxyGathering)
from GoodsReceipt group by convert(varchar(10),dtEdit,120)
union all
select dtEdit=convert(varchar(10),dtEdit,120),mPayoutGoodsMoney=-sum(mPayoutGoodsMoney)
from GoodsPayout group by convert(varchar(10),dtEdit,120))a
select dtEdit,
mReceiptProxyGathering=(select sum(mReceiptProxyGathering) from tab where dtEdit<=a.dtEdit)
from tab a
group by dtEdit--結果
dtEdit mReceiptProxyGathering
-------------------------------------
2004-11-01 1000.0000
2004-11-02 2200.0000
2004-11-03 3200.0000
2004-11-04 4400.0000
2004-11-05 4400.0000
2004-11-11 3400.0000
--刪除臨時表
drop table tab