表1 :table1 表2:table2 表1 字段 id num
表2 字段 id num表1数据
id num
1 100
2 100
3 100
4 200
5 200表2
id num
2 30
4 20
2 20现在想得到表1减去表2的量后的值 id num
1 100
2 50
3 100
4 180
5 200
这样的select语句怎么写?
表2 字段 id num表1数据
id num
1 100
2 100
3 100
4 200
5 200表2
id num
2 30
4 20
2 20现在想得到表1减去表2的量后的值 id num
1 100
2 50
3 100
4 180
5 200
这样的select语句怎么写?
from table1 a,table2 b
where a.id=b.id(+)
group by a.id
select id,sum(num) num from table1 group by id) a
left join
(
select id,sum(num) num from table2 group by id) b
on a.id=b.id order by id
from table1 a
left outer join (select id, sum(num) as num from table2 group by id) b on a.id = b.id
order by a.id
from (select id, num
from table1
union all
select id, -num from table2)
group by id
from (select id, num
from table1
union all
select id, -num from table2)
group by id
select id, sum(num) as num
from (select id, sum(num) num
from table1
group by id
union all
select id, sum(-num) num
from table2
group by id)
group by id
select id, sum(num) as num
from (select id, num
from table1
union all
select id, -num num from table2)
group by id
已连接。
SQL> create table table1 (id number,num number);表已创建。SQL> create table table2 (id number,num number);表已创建。SQL> insert into table1 values(1,100);已创建 1 行。SQL> insert into table1 values(2,100);已创建 1 行。SQL> insert into table1 values(3,100);已创建 1 行。SQL> insert into table1 values(4,200);已创建 1 行。SQL> insert into table1 values(5,200);已创建 1 行。SQL> insert into table2 values(2,30);已创建 1 行。SQL> insert into table2 values(2,20);已创建 1 行。SQL> insert into table2 values(4,20);已创建 1 行。SQL>
SQL> select * from table1; ID NUM
---------- ----------
1 100
2 100
3 100
4 200
5 200SQL> select * from table2; ID NUM
---------- ----------
2 30
2 20
4 20SQL> select id,sum(num) num from (select id,num from table1 union all
2 select id,-num from table2)
3 group by id; ID NUM
---------- ----------
1 100
2 50
4 180
5 200
3 1005楼的正确
create table a(id int,num int);
insert into a values(1,100);
insert into a values(2,100);
insert into a values(3,100);
insert into a values(4,200);
insert into a values(5,200);
drop table b;
create table b(id int,num int);
insert into b values(2,30);
insert into b values(4,20);
insert into b values(2,20);SQL> select a.id,a.num-nvl(c.num,0) from a
2 left join
3 (select id,sum(num) num from b
4 group by id) c
5 on a.id=c.id
6 order by a.id; ID A.NUM-NVL(C.NUM,0)
---------- ------------------
1 100
2 50
3 100
4 180
5 200
create table table2 (id int,num int);
insert into table1 values(1,100);
insert into table1 values(2,100);
insert into table1 values(3,100);
insert into table1 values(4,200);
insert into table1 values(5,200);insert into table2 values(2,30);
insert into table2 values(4,20);
insert into table2 values(2,20);
commit;
select * from table1 t1;
select * from table2 t2;
select id, sum(num)
from (select t1.id, t1.num
from table1 t1
union all
select t2.id, -t2.num from table2 t2) t
group by id
order by id
table1 a,(select id ,sum(num) num from table2 group by id ) b
where a.id = b.id(+)
FROM TABLE1 a,(SELECT ad,SUM(num) num FROM TABLE2 GROUP BY ad ) b
WHERE a.ad=b.ad(+)
create table t1(id int,num int)
insert into t1
select 1,100 from dual
union all
select 2,100 from dual
union all
select 3,100 from dual
union all
select 4,100 from dual
union all
select 5,100 from dual;create table t2(id int,num int)
insert into t2
select 2,30 from dual
union all
select 4,20 from dual
union all
select 2,20 from dualselect a.id,a.num- (case when b.num is not null then b.num else 0 end) from t1 a left join
(select id,sum(num) as num from t2 group by id) b
on a.id=b.id
order by a.id