我的贴子打开怎么变成 '发生错误' 了?? 就是" 求一个关于应付帐款的Select语句"这个贴子,你们能打开吗?

Question

这里重发一下:应付已付表:V_Material_Payment_IO 
方向　 凭证号      摘要      日期        供应商      应付金额      已付金额 
IorO　WarrantNo　Explain　IODate　    IOCompany　ShouldMoney　AlreadyMoney 
----------------------------------------------------------------------------- 
应付  001        购料    2008-01-01  供应商A    3000.00 
应付  002        购料    2008-01-03  供应商A    4000.00 
应付  003        购料    2008-01-06  供应商A    7000.00 
应付  004        电汇    2008-01-08  供应商A                10000.00 应付  005        购料    2008-02-01  供应商A    4000.00 
应付  006        购料    2008-02-03  供应商A    5000.00 
应付  007        购料    2008-02-06  供应商A    6000.00 
已付  008        现金    2008-02-08  供应商A                12000.00 应付  009        购料    2008-02-01  供应商B    1000.00 
应付  010        购料    2008-02-03  供应商B    2000.00 
应付  011        购料    2008-02-06  供应商B    3000.00 
已付  012        电汇    2008-02-08  供应商B                7000.00 年度结转表:TableA 
YearNo IOCompany  RemainMoney (余额) 
-------------------------------- 
2007  供应商A    3000.00 
2007  供应商B    2000.00 ------------我想得到[供应商A]这样的结果-------------- 
发生日期      凭证号    摘要      应付金额      已付金额      余额 
                        上年结转                                3000.00 
2008-01-01  001      购料      3000.00                  6000.00 
2008-01-03  002      购料      4000.00                  10000.00 
2008-01-06  003      购料      7000.00                  17000.00 
2008-01-08  004      电汇                    10000.00      7000.00 
                      本月合计  14000.00    10000.00      7000.00 
                      累计      14000.00    10000.00      7000.00 
2008-02-01  005      购料      4000.00                  11000.00 
2008-02-03  006      购料      5000.00                  16000.00 
2008-02-06  007      购料      6000.00                  22000.00 
2008-02-08  008      现金                    12000.00    10000.00 
                      本月合计  15000.00    12000.00    10000.00 
                      本年累计  29000.00    22000.00    10000.00 

Answer 1

 
/******************************************************************************/
/*回复：20080624002总：00075                                                   */
/*主题：会计分录                                                                          */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
 
create table [tb] ([IorO] varchar(4),[WarrantNo] varchar(3),[Explain] varchar(4),[IODate] datetime,[IOCompany] varchar(7)
,[ShouldMoney] numeric(6,2),[AlreadyMoney] numeric(7,2))
 insert into [tb] select '应付','001','购料','2008-01-01','供应商A',3000.00,null
 insert into [tb] select '应付','002','购料','2008-01-03','供应商A',4000.00,null
 insert into [tb] select '应付','003','购料','2008-01-06','供应商A',7000.00,null
 insert into [tb] select '应付','004','电汇','2008-01-08','供应商A',0,10000.00
 insert into [tb] select '应付','005','购料','2008-02-01','供应商A',4000.00,null
 insert into [tb] select '应付','006','购料','2008-02-03','供应商A',5000.00,null
 insert into [tb] select '应付','007','购料','2008-02-06','供应商A',6000.00,null
 insert into [tb] select '已付','008','现金','2008-02-08','供应商A',0,12000.00
 insert into [tb] select '应付','009','购料','2008-02-01','供应商B',1000.00,null
 insert into [tb] select '应付','010','购料','2008-02-03','供应商B',2000.00,null
 insert into [tb] select '应付','011','购料','2008-02-06','供应商B',3000.00,null
 insert into [tb] select '已付','012','电汇','2008-02-08','供应商B',0,7000.00
 
create table [Ta] ([YearNo] int,[IOCompany] varchar(7),[RemainMoney] numeric(6,2))
 insert into [Ta] select 2007,'供应商A',3000.00
 insert into [Ta] select 2007,'供应商B',2000.00
go--代码--------------------------------------------------------------------------
select a.*
,remainMoney=isnull(b.remainMoney,0) + (select isnull(sum(ShouldMoney),0)-isnull(sum(AlreadyMoney),0) from tb 
                              where IOCompany = a.IOCompany and WarrantNo <=a.WarrantNo)
from tb a left join ta b on a.IOCompany = b.IOCompany
go/*结果--------------------------------------------------------------------------
IorO WarrantNo Explain IODate                                                 IOCompany ShouldMoney AlreadyMoney remainMoney                              
---- --------- ------- ------------------------------------------------------ --------- ----------- ------------ ---------------------------------------- 
应付   001       购料      2008-01-01 00:00:00.000                                供应商A          3000.00 NULL                                          6000.00 
应付   002       购料      2008-01-03 00:00:00.000                                供应商A          4000.00 NULL                                         10000.00 
应付   003       购料      2008-01-06 00:00:00.000                                供应商A          7000.00 NULL                                         17000.00 
应付   004       电汇      2008-01-08 00:00:00.000                                供应商A              .00     10000.00                                  7000.00 
应付   005       购料      2008-02-01 00:00:00.000                                供应商A          4000.00 NULL                                         11000.00 
应付   006       购料      2008-02-03 00:00:00.000                                供应商A          5000.00 NULL                                         16000.00 
应付   007       购料      2008-02-06 00:00:00.000                                供应商A          6000.00 NULL                                         22000.00 
已付   008       现金      2008-02-08 00:00:00.000                                供应商A              .00     12000.00                                 10000.00 
应付   009       购料      2008-02-01 00:00:00.000                                供应商B          1000.00 NULL                                          3000.00 
应付   010       购料      2008-02-03 00:00:00.000                                供应商B          2000.00 NULL                                          5000.00 
应付   011       购料      2008-02-06 00:00:00.000                                供应商B          3000.00 NULL                                          8000.00 
已付   012       电汇      2008-02-08 00:00:00.000                                供应商B              .00      7000.00                                  1000.00 --清除------------------------------------------------------------------------*/
drop table tb,ta

Answer 2

 
/******************************************************************************/
/*回复：20080624002总：00075                                                   */
/*主题：会计分录                                                                          */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
 
create table [tb] ([IorO] varchar(4),[WarrantNo] varchar(3),[Explain] varchar(10),[IODate] datetime,[IOCompany] varchar(7)
,[ShouldMoney] numeric(8,2),[AlreadyMoney] numeric(8,2))
 insert into [tb] select '应付','001','购料','2008-01-01','供应商A',3000.00,null
 insert into [tb] select '应付','002','购料','2008-01-03','供应商A',4000.00,null
 insert into [tb] select '应付','003','购料','2008-01-06','供应商A',7000.00,null
 insert into [tb] select '应付','004','电汇','2008-01-08','供应商A',0,10000.00
 insert into [tb] select '应付','005','购料','2008-02-01','供应商A',4000.00,null
 insert into [tb] select '应付','006','购料','2008-02-03','供应商A',5000.00,null
 insert into [tb] select '应付','007','购料','2008-02-06','供应商A',6000.00,null
 insert into [tb] select '已付','008','现金','2008-02-08','供应商A',0,12000.00
 insert into [tb] select '应付','009','购料','2008-02-01','供应商B',1000.00,null
 insert into [tb] select '应付','010','购料','2008-02-03','供应商B',2000.00,null
 insert into [tb] select '应付','011','购料','2008-02-06','供应商B',3000.00,null
 insert into [tb] select '已付','012','电汇','2008-02-08','供应商B',0,7000.00
 
create table [Ta] ([YearNo] int,[IOCompany] varchar(7),[RemainMoney] numeric(6,2))
 insert into [Ta] select 2007,'供应商A',3000.00
 insert into [Ta] select 2007,'供应商B',2000.00
go--代码--------------------------------------------------------------------------
--发生日期      凭证号    摘要      应付金额      已付金额      余额 select xh = warrantno* 10,rq=convert(char(10),IODate,120),pzh=warrantno,zy = explain,sm = ShouldMoney,am=AlreadyMoney
,ye=isnull(b.remainMoney,0) + (select isnull(sum(ShouldMoney),0)-isnull(sum(AlreadyMoney),0) from tb 
                              where IOCompany = a.IOCompany and WarrantNo <=a.WarrantNo)
into #
from tb a left join ta b on a.IOCompany = b.IOCompany where a.IOCompany = '供应商A'insert # select xh = max(xh)+1,rq = null,pzh = null,zy = '本月合计',sm=sum(sm),am=sum(am)
,ye=(select top 1 ye from # where convert(char(7),rq,120) = convert(char(7),a.rq,120) order by xh desc) 
from # a group by convert(char(7),rq,120)insert # select xh = a.xh+1,rq = null,pzh=null,zy='本月累计'
,sm = (select sum(sm) from # where xh%10=1 and xh<=a.xh)
,am = (select sum(sm) from # where xh%10=1 and xh<=a.xh)
,ye = ye
from # a where xh%10=1insert # select xh = max(xh)+1,rq = null,pzh = null,zy = '本年累计',sm=sum(sm),am=sum(am)
,ye=(select top 1 ye from #  order by xh desc) 
from # insert # select xh = 0,rq = null,pzh = null,zy='上年结转',sm=null,am=null,ye = RemainMoney 
from ta where IOCompany = '供应商A'select 发生日期=rq,凭证号=pzh,摘要=zy,应付金额=sm,已付金额=am,余额=ye from # order by xh
drop table #
go/*结果--------------------------------------------------------------------------
发生日期       凭证号  摘要         应付金额       已付金额       余额                                       
---------- ---- ---------- ---------- ---------- ---------------------------------------- 
NULL       NULL 上年结转       NULL       NULL                                        3000.00 
2008-01-01 001  购料            3000.00 NULL                                        6000.00 
2008-01-03 002  购料            4000.00 NULL                                       10000.00 
2008-01-06 003  购料            7000.00 NULL                                       17000.00 
2008-01-08 004  电汇                .00   10000.00                                  7000.00 
NULL       NULL 本月合计         14000.00   10000.00                                  7000.00 
NULL       NULL 本月累计         14000.00   14000.00                                  7000.00 
2008-02-01 005  购料            4000.00 NULL                                       11000.00 
2008-02-03 006  购料            5000.00 NULL                                       16000.00 
2008-02-06 007  购料            6000.00 NULL                                       22000.00 
2008-02-08 008  现金                .00   12000.00                                 10000.00 
NULL       NULL 本月合计         15000.00   12000.00                                 10000.00 
NULL       NULL 本月累计         29000.00   29000.00                                 10000.00 
NULL       NULL 本年累计        101000.00   87000.00                                 10000.00 
--清除------------------------------------------------------------------------*/
drop table tb,ta

Answer 3

前面有些错误，修正如下：
 
/******************************************************************************/
/*回复：20080624002总：00075                                                   */
/*主题：会计分录                                                                          */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
 
create table [tb] ([IorO] varchar(4),[WarrantNo] varchar(3),[Explain] varchar(10),[IODate] datetime,[IOCompany] varchar(7)
,[ShouldMoney] numeric(8,2),[AlreadyMoney] numeric(8,2))
 insert into [tb] select '应付','001','购料','2008-01-01','供应商A',3000.00,null
 insert into [tb] select '应付','002','购料','2008-01-03','供应商A',4000.00,null
 insert into [tb] select '应付','003','购料','2008-01-06','供应商A',7000.00,null
 insert into [tb] select '应付','004','电汇','2008-01-08','供应商A',0,10000.00
 insert into [tb] select '应付','005','购料','2008-02-01','供应商A',4000.00,null
 insert into [tb] select '应付','006','购料','2008-02-03','供应商A',5000.00,null
 insert into [tb] select '应付','007','购料','2008-02-06','供应商A',6000.00,null
 insert into [tb] select '已付','008','现金','2008-02-08','供应商A',0,12000.00
 insert into [tb] select '应付','009','购料','2008-02-01','供应商B',1000.00,null
 insert into [tb] select '应付','010','购料','2008-02-03','供应商B',2000.00,null
 insert into [tb] select '应付','011','购料','2008-02-06','供应商B',3000.00,null
 insert into [tb] select '已付','012','电汇','2008-02-08','供应商B',0,7000.00
 
create table [Ta] ([YearNo] int,[IOCompany] varchar(7),[RemainMoney] numeric(6,2))
 insert into [Ta] select 2007,'供应商A',3000.00
 insert into [Ta] select 2007,'供应商B',2000.00
go--代码--------------------------------------------------------------------------
--发生日期      凭证号    摘要      应付金额      已付金额      余额 select xh = warrantno* 10,rq=convert(char(10),IODate,120),pzh=warrantno,zy = explain,sm = ShouldMoney,am=AlreadyMoney
,ye=isnull(b.remainMoney,0) + (select isnull(sum(ShouldMoney),0)-isnull(sum(AlreadyMoney),0) from tb 
                              where IOCompany = a.IOCompany and WarrantNo <=a.WarrantNo)
into #
from tb a left join ta b on a.IOCompany = b.IOCompany where a.IOCompany = '供应商A'insert # select xh = max(xh)+1,rq = null,pzh = null,zy = '本月合计',sm=sum(sm),am=sum(am)
,ye=(select top 1 ye from # where convert(char(7),rq,120) = convert(char(7),a.rq,120) order by xh desc) 
from # a group by convert(char(7),rq,120)insert # select xh = a.xh+1,rq = null,pzh=null,zy='累计'
,sm = (select sum(sm) from # where xh%10=1 and xh<=a.xh)
,am = (select sum(am) from # where xh%10=1 and xh<=a.xh)
,ye = ye
from # a where xh%10=1update # set zy= '本年累计' where xh = (select max(xh) from #)insert # select xh = 0,rq = null,pzh = null,zy='上年结转',sm=null,am=null,ye = RemainMoney 
from ta where IOCompany = '供应商A'select 发生日期=rq,凭证号=pzh,摘要=zy,应付金额=sm,已付金额=am,余额=ye from # order by xh
drop table #
go/*结果--------------------------------------------------------------------------
发生日期       凭证号  摘要         应付金额       已付金额       余额                                       
---------- ---- ---------- ---------- ---------- ---------------------------------------- 
NULL       NULL 上年结转       NULL       NULL                                        3000.00 
2008-01-01 001  购料            3000.00 NULL                                        6000.00 
2008-01-03 002  购料            4000.00 NULL                                       10000.00 
2008-01-06 003  购料            7000.00 NULL                                       17000.00 
2008-01-08 004  电汇                .00   10000.00                                  7000.00 
NULL       NULL 本月合计         14000.00   10000.00                                  7000.00 
NULL       NULL 累计           14000.00   10000.00                                  7000.00 
2008-02-01 005  购料            4000.00 NULL                                       11000.00 
2008-02-03 006  购料            5000.00 NULL                                       16000.00 
2008-02-06 007  购料            6000.00 NULL                                       22000.00 
2008-02-08 008  现金                .00   12000.00                                 10000.00 
NULL       NULL 本月合计         15000.00   12000.00                                 10000.00 
NULL       NULL 本年累计         29000.00   22000.00                                 10000.00 --清除------------------------------------------------------------------------*/
drop table tb,ta

Answer 4

谢谢二等草!
你可不可以再帮我改进一下,因为字段 WarrantNo 不能用来处理顺序,因为这是凭证号用的,里面的内容是不规范的,有中文字,也有英文,也有可能为空.