update com_total set i_num=br.num from com_total, (select ar.x,count(ar.x) as num from ( select left (entry_id, 2) as x, entry_id from im_entry_head ) as ar group by ar.x ) as br where ar.x=com_total.com_id
不好意思,写错了:(where br.x=com_total.com_id
progame(来来往往,不知所云.......)的方法有道理,对表COM_TOTAL要统计的字段逐个处理,每次去查询一个商品单证表,一条sql语句就可以搞定,不用按子公司进行循环。这样执行30条左右的sql语句就可以搞定了。如果将商品单证表的表名设计得特殊一点,比如前面增加一个en_前缀,就可以通过sysobjects表来写一个循环语句,不用手写30条sql语句了。 另:以上语句可以再简单一点: update a set i_num = b.num from com_total a, ( select left(entry_id,2) as id,count(entry_id) as num from im_entry_head group by left(entry_id,2) ) as b where a.com_id = b.id
com_total,
(select ar.x,count(ar.x) as num
from ( select left (entry_id, 2) as x, entry_id from im_entry_head )
as ar group by ar.x ) as br
where ar.x=com_total.com_id
另:以上语句可以再简单一点:
update a set i_num = b.num
from com_total a,
(
select left(entry_id,2) as id,count(entry_id) as num
from im_entry_head
group by left(entry_id,2)
) as b
where a.com_id = b.id