表A 有id , value 两列 ,值为下:
id value
2 0.2
2 0.2
2 0.2
3 0.1
3 0.3
4 0.1
4 0.2
5 0.2
5 0.1
7 0.2
7 0.3
7 0.1我现在想根据id来求对应的value平均值,例如id为2的有3条记录,那么value平均值为0.2+0.2+0.2/3=0.2
id为2的有2条记录,那么对应value平均值为0.1+0.3/2=0.2
实际上value平均值=该id对应value值的和/id记录条数然后把表A对应value值全部更新为求出的value平均值
请高手指教。想半天没写出来
id value
2 0.2
2 0.2
2 0.2
3 0.1
3 0.3
4 0.1
4 0.2
5 0.2
5 0.1
7 0.2
7 0.3
7 0.1我现在想根据id来求对应的value平均值,例如id为2的有3条记录,那么value平均值为0.2+0.2+0.2/3=0.2
id为2的有2条记录,那么对应value平均值为0.1+0.3/2=0.2
实际上value平均值=该id对应value值的和/id记录条数然后把表A对应value值全部更新为求出的value平均值
请高手指教。想半天没写出来
SET VALUE =
(SELECT v FROM (SELECT id, AVG(VALUE) v FROM 表A GROUP BY id) b WHERE b.id = a.id);
merge into A using(select id,avg(value) value
from A group by id) B on(A.id=B.id)
when matched then update set A.value=B.value
SET VALUE =
(SELECT v FROM (SELECT id, AVG(VALUE) v FROM 表 GROUP BY id) b WHERE b.id = a.id);
set t1.value=(select avg(nvl(t2.value,0))
from a t2
where t2.id=t1.id
group by t2.id );
-- 更新员工工资为部门的平均工资
update emp t1
set t1.sal=(select avg(nvl(t2.sal,0))
from emp t2
where t2.deptno=t1.deptno
group by t2.deptno );