这边高手比较多，请教一下sql语句整合简化的问题

ryr1990

表建立：
table  A
(
lsh char(20),
je  number
)

table B
(
xh1 char(10),
xh2 char(10),
lsh char(20),
je number,
inum int,
bwh char(10),
)

1.建立临时表1
select xh1,xh2,sum(je) je1 from
        (
        select b.xh1,b.xh2,a.je
        from  
        (select je from A  where lsh in (select lsh from B where inum=10)) a,
        B b
        where a.lsh=b.lsh
        )
group by xh1,xh2   into temp tmp_table1;

2.建立临时表2
select xh1,xh2,sum(je) je2 from B where bwh='10100' group by xh1,xh2  into temp tmp_table2;

3.临时表做并集
select a.je1,b.je2 from tmp_table1 a,tmp_table2 b where a.xh1=b.xh1 and a.xh2=b.xh2;

请问有什么好的方法把上面3个语句整合到1条语句实现？

li0924

LZ你来错了吧；
你第一条sql意思其实就是这个

1. select b.xh1,b.xh2,sum(je) je1 from A a,B b where a.lsh=b.lsh and b.inum=10 group by b.xh1,b.xh2

复制代码

可是你的结果这样的话；

1. select t1.je1,t2.je2 from
   
2. (select b.xh1,b.xh2,sum(je) je1 from A a,B b where a.lsh=b.lsh and b.inum=10 group by b.xh1,b.xh2) t1
   
3. full join
   
4. (select xh1,xh2,sum(je) je2 from B where bwh='10100' group by xh1,xh2) t2
   
5. on t1.xh1=t2.xh1 and t1.xh2=t2.xh2;

复制代码

yestreenstars

回复 2# li0924

撞枪口上了~
   

ryr1990

回复 2# li0924

恩，这样可以实现。那不用join可以吗？比如下面的问题
http://bbs.chinaunix.net/thread-4126374-1-1.html