求解一道题，不难，急！分100，万谢

Question

你试试，我这里不能试  select a.xh,sum(a.cj*b.xf)/sum(b.xf)
from xk a,kc b
where a.kch=b.kch
group by a.xh

Answer 1

--这个可以吗?update xs set px=t.绩点
from (select xk.xh,sum(xk.cj*kc.xf)/sum(kc.xf) 绩点 from xk join kc on xk.kch=kc.kch
      group by xk.xh) t join xs on t.xh=xs.xh

Answer 2

mschen(老陈)  写的好 我以为查询呢

Answer 3

select a.xh,sum(a.cj*b.xf)/sum(b.xf) as 绩点
from xk a,kc b
where a.kch=b.kch
group by a.xh
order by 绩点 desc

Answer 4

--添加新的列  alter table xs add  px decimal(4,1)--执行   update xs set px=t.绩点
   from (select xk.xh,sum(xk.cj*kc.xf)/sum(kc.xf) 绩点 from xk join kc on xk.kch=kc.kch
         group by xk.xh) t 
   join xs on t.xh=xs.xh--测试结果
/*
xh       xm       others                                                                                               px     
-------- -------- ---------------------------------------------------------------------------------------------------- ------ 
00010001 zxx      ......                                                                                               86.2
00010002 lxx      ......                                                                                               89.5
00010003 wxx      ......                                                                                               89.5
00010004 qxx      ......                                                                                               NULL（所影响的行数为 4 行）
*/