select fguid,fprojectid,fprojectname,type, (select sum(price) from tab2 where fprojectid=t.fprojectid) field1 from tab1 t;
试试这样: update 表1 t1 set Filed1 = (select p from (select type,sum(price) p from 表2) t2 where t1.type=t2.type)
select a.FGUID,a.FprojectID,a.FprojectName,a.type,b.sum_price from 表1 a,(select FprojectID,type,sum(price) sum_price from 表2 group by FprojectID,type) b where a.FprojectID=b.FprojectID and a.type=b.type;
如果只是查询出结果: select t1.FGUID,t1.FprojectID,t1.FprojectName,t1.type,t2.p Filed1 from 表1 t1,(select type,sum(price) p from 表2) t2 where t1.type=t2.type order by t1.FprojectID,t1.type;FprojectID表1、表2没有对应关系?
select t1.fguid,t1.fprojectid,t1.fprojectname,t1.type,sum(t2.field1) from 表1 t1,表2 t2 where t1.fprojectid = t2.projectid group by t1.fguid,t1.fprojectid,t1.fprojectname,t1.type
描述不清楚--猜的SQL> with tb as 2 ( 3 select 1 FGUID,001 FprojectID,'项目1' FprojectName ,'a' type,null Field1 from dual union all 4 select 2,002,'项目2','b',null from dual 5 ), 6 tb2 as 7 (select 001 FprojectID,'a' type,1 price from dual union all 8 select 001 FprojectID,'a' type,2 price from dual union all 9 select 001 FprojectID,'b' type,5 price from dual ) 10 select a.FGUID,a.FprojectID,a.FprojectName,a.type,b.Field1 11 from tb a,(select FprojectID,type,sum(price) Field1 from tb2 group by FprojectID,type) b 12 where a.FprojectID=b.FprojectID 13 / FGUID FPROJECTID FPROJ T FIELD1 ---------- ---------- ----- - ---------- 1 1 项目1 a 3 1 1 项目1 a 5
(select sum(price) from tab2 where fprojectid=t.fprojectid) field1
from tab1 t;
update 表1 t1
set Filed1 =
(select p
from (select type,sum(price) p from 表2) t2
where t1.type=t2.type)
from 表1 a,(select FprojectID,type,sum(price) sum_price from 表2 group by FprojectID,type) b
where a.FprojectID=b.FprojectID and a.type=b.type;
select t1.FGUID,t1.FprojectID,t1.FprojectName,t1.type,t2.p Filed1
from 表1 t1,(select type,sum(price) p from 表2) t2
where t1.type=t2.type
order by t1.FprojectID,t1.type;FprojectID表1、表2没有对应关系?
from 表1 t1,表2 t2
where t1.fprojectid = t2.projectid
group by t1.fguid,t1.fprojectid,t1.fprojectname,t1.type
2 (
3 select 1 FGUID,001 FprojectID,'项目1' FprojectName ,'a' type,null Field1 from dual union all
4 select 2,002,'项目2','b',null from dual
5 ),
6 tb2 as
7 (select 001 FprojectID,'a' type,1 price from dual union all
8 select 001 FprojectID,'a' type,2 price from dual union all
9 select 001 FprojectID,'b' type,5 price from dual )
10 select a.FGUID,a.FprojectID,a.FprojectName,a.type,b.Field1
11 from tb a,(select FprojectID,type,sum(price) Field1 from tb2 group by FprojectID,type) b
12 where a.FprojectID=b.FprojectID
13 / FGUID FPROJECTID FPROJ T FIELD1
---------- ---------- ----- - ----------
1 1 项目1 a 3
1 1 项目1 a 5