select a.id ID,a.building BuildingID,a.sumroomarea*b.areaprice TotalPrice from
(
select id,buildingid,sum(roomarea) sumroomarea from buildinfo
) a,areaprice b
where a.id=b.id and a.buildingid=b.buildingid
(
select id,buildingid,sum(roomarea) sumroomarea from buildinfo
) a,areaprice b
where a.id=b.id and a.buildingid=b.buildingid
ORARichard(没钱的日子......)的思路正确,但有一个小失误!!
select a.id ID,a.buildingid BuildingID,a.sumroomarea*b.areaprice TotalPrice from
(
select id,buildingid,sum(roomarea) sumroomarea from buildinfo
) a,areaprice b
where a.id=b.id and a.buildingid=b.buildingid
有个问题,最后查出的结构显示谁的id呢?你看一下他的两个表的id,这样做即使查出来也不会出现期望的结果的。
select b.id ID,b.buildingid BuildingID,a.sumroomarea*b.areaprice TotalPrice from
(
select sum(roomarea) sumroomarea from buildinfo
) a,areaprice b
where a.buildingid=b.buildingid
看需求应该就是要计算出某楼的totalprice
她想得到的结果,应该也是以B表作主表的。不能用id来作关联。应该用BuildingID。同意 bisliu(努力学习oracle!) 的想法。
(
select buildingid,sum(roomarea) sumroomarea from buildinfo
group by buildingid
) a,areaprice b
where a.buildingid(+)=b.buildingid
不过 rownum ID 应变成 b.ID 有点意义
(
select buildingid,sum(roomarea) sumroomarea from buildinfo
group by buildingid
) a,areaprice b
where a.buildingid(+)=b.buildingidorder by b.buildingid
(
select buildingid,sum(roomarea) sumroomarea from buildinfo
group by buildingid
) a
right join areaprice b
on a.buildingid=b.buildingid