update T set T.ITEM_TOTAL = V.CNT from TABLE_A T, (select a.ITEM_NO,count(b.ITEM_NO) as CNT from TABLE_A a,TABLE_B b where a.ITEM_NO=b.ITEM_NO group by a.ITEM_NO) V where T.ITEM_NO = V.ITEM_NO
select ITEM_NO ,count(ITEM_NO ) as ITEM_TOTAL from TABLE_B group by ITEM_NO
set T.ITEM_TOTAL = V.CNT
from
TABLE_A T,
(select
a.ITEM_NO,count(b.ITEM_NO) as CNT
from
TABLE_A a,TABLE_B b
where
a.ITEM_NO=b.ITEM_NO
group by
a.ITEM_NO) V
where
T.ITEM_NO = V.ITEM_NO
TABLE_A
ITEM_NO ITEM_TOTAL
A001
A002 TABLE_B
ITEM_NO ITEM_QTY
A001 1
A001 1
A001 1
A002 2
A002 2
----------------------期待结果
ITEM_NO ITEM_TOTAL
A001 3
A002 4(抱歉,刚才敲错了。)
libin_ftsafe(子陌红尘) 的那么长, wyb0026(小小) 的那么简练,哪个好?
select ITEM_NO,count(ITEM_NO) ITEM_TOTAL from A,B where A.ITEM_NO = B.ITEM_NO group by ITEM_NO
set item_total=sum(select ITEM_QTY from table_b)