请教各位如何实现这两个结果。(100分)

在sql server 2000后台有一个要修改t_fn_zhechangfuzhaibiao表,表数据如下<br>code &nbsp; &nbsp;zichang &nbsp; &nbsp; &nbsp; &nbsp; hanci &nbsp; &nbsp;qumoshu &nbsp; &nbsp; &nbsp; gongsi<br>2 &nbsp; &nbsp; &nbsp; 货币资金 &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;17737.56 &nbsp; &nbsp; &nbsp;a101<br>3 &nbsp; &nbsp; &nbsp; 短期借款 &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp;45390.56 &nbsp; &nbsp; &nbsp;C101:109<br>4 &nbsp; &nbsp; &nbsp; 应付票据 &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b101001,109,111<br>5 &nbsp; &nbsp; &nbsp; 短期投资 &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b101001,109,111<br>6 &nbsp; &nbsp; &nbsp; 应付账款 &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp;17737.56 &nbsp; &nbsp; &nbsp;a101<br>7 &nbsp; &nbsp; &nbsp; 应收票据 &nbsp; &nbsp; &nbsp; &nbsp;6 &nbsp; &nbsp; &nbsp; &nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b101002,102,112<br>8 &nbsp; &nbsp; &nbsp; 应收账款 &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp;8223703.52 &nbsp; &nbsp;a113<br>9 &nbsp; &nbsp; &nbsp; 总计 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8 &nbsp; &nbsp; &nbsp; &nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;d2,3,5,6,7,8<br>这个表根据gongsi(varchar型)的a,b,c,d开头的四种方法计算来实现qumoshu(decimal型)的结果,其中,a和c两种方式本人已经实现,但b和d两种方式还未实现.本人请教如何实现b和d两种计算方式<br>1.b类型的计算.b类是根据编号模糊查询,假设gongsi例其中一个数据是101,102,109,而要计算的方式是根据表t_bs_accountingsubject所有以101,102,109开头的编号,例如101001,1010002,102003,109005等编号开始的一行数据,本人写的语句如下:<br> update t_fn_zhechangfuzhaibiao set qumoshu(select case b.direction when 1 then sum(a.debitamount)-sum(a.creditamount) when -1 then sum(a.creditamount)-sum(a.debitamount) end<br> &nbsp; from t_fn_voucher_detail as a <br> &nbsp;left join t_bs_accountingsubject as b on b.id=a.bs_accountingsubject_id <br> &nbsp;left join t_fn_voucher as c on c.id=a.fn_voucher_id <br>where b.code in ((gongsi)+'%'))<br> &nbsp;group by b.direction<br>但是语句没能实现结果.<br>2.d类型的计算,根据d以后的code号码汇总所有qumoshu的数据,例如d2,3,5,6,7,8是code为2,3,5,6,7行的qumoshu相加.本人写的语句如下:<br> &nbsp;update t_fn_zhechangfuzhaibiao set qumoshu=(select sum(a.qumoshu) from t_fn_zhechangfuzhaibiao as a where a.code in <br> &nbsp;(substring(t_fn_zhechangfuzhaibiao.gongsi,2,len(t_fn_zhechangfuzhaibiao.gongsi)-1)))<br> &nbsp;where left(gongsi,1)='d'<br>但语句未能实现结果,提示是<br>服务器: 消息 245，级别 16，状态 1，行 1<br>将 varchar 值 '2,3,5,6,7,8' 转换为数据类型为 int 的列时发生语法错误。<br>因为gongsi是varchar型.我想把code化为varchar型,但是也不行,语句如下<br> &nbsp;update t_fn_zhechangfuzhaibiao set qumoshu=(select sum(a.qumoshu) from t_fn_zhechangfuzhaibiao as a where cast(a.code as varchar(10)) in <br> &nbsp;(substring(t_fn_zhechangfuzhaibiao.gongsi,2,len(t_fn_zhechangfuzhaibiao.gongsi)-1)))<br> &nbsp;where left(gongsi,1)='d'<br>出现的是空值.<br><br>请教各位如何实现这两个结果。

出现NULL的可能性是您的sum(a.qumoshu)要改为sum(isnull(a.qumoshu,0))

不是sum(a.qumoshu)， sum(a.qumoshu)会忽略null；<br>是关联产生的问题。