我有两个表(苹果表和梨子表):苹果表:
NO FDM(分店名) PGSL(苹果数量)
1 分店1 100
2 分店2 100
3 分店3 100梨子表:
NO FDM(分店名) LZSL(梨子数量)
1 分店2 100
2 分店3 100
3 分店4 100我现在想得到这样的视图:
水果总数视图:NO FDM(分店名) PGSL(苹果数量) LZSL(梨子数量) 合计数量
1 分店1 100 0 100
2 分店2 100 100 200
3 分店3 100 100 200
4 分店4 0 100 100不知创建这样的视图的SQL语句怎么写????
求教各位高手!!!!!!
请帮我写出具体sql语句,不胜感激!!!!!!!!!!!!!!!
select fdm,pgsl,lzsl,pgsl+lzsl hjsl from(
select tb1.fdm,pgsl,lzsl from tb1,tb2 where tb1.fdm=tb2.fdm(+)
union all
select tb2.fdm,pgsl,lzsl from tb1,tb2 where tb1.fdm(+)=tb2.fdm
and pgsl is null);
,sum(c.pgsl)+sum(c.lzsl) AS total FROM
(
SELECT a.fdm,a.pgsl,0 AS lzsl FROM apple_table a
UNION ALL
SELECT b.fdm ,0 AS pgsl,b.lzsl FROM pear_table b
)c
from 苹果表
order by fdm
union
Select Rownum as no,fdm as 分店名,0 as 苹果数量,lzsl as 梨子数量,lzsl as 合计数量
from 梨子表
order by fdm就这么简单