两张表
create table TEST1
(
name char(20),
money1 integer
);
create table TEST1
(
name char(20),
money2 integer
)
表TEST1中的数据:
insert into TEST1 values('001',3000')
insert into TEST1 values('001',100')
insert into TEST1 values('001',3500')
insert into TEST1 values('002',700')
insert into TEST1 values('002',4000')表TEST2中的数据:
insert into TEST2 values('001',7000')
insert into TEST2 values('001',200')
insert into TEST2 values('001',500')
insert into TEST2 values('002',1700')
insert into TEST2 values('002',1200')请写出一个SQL查询出 TEST2 中 name 相同的 money1 和 减去 相同name 的 TEST1表中的 money2
如以上结果应该为:
select .......结果为:name result
001 1100
002 -1800
create table TEST1
(
name char(20),
money1 integer
);
create table TEST1
(
name char(20),
money2 integer
)
表TEST1中的数据:
insert into TEST1 values('001',3000')
insert into TEST1 values('001',100')
insert into TEST1 values('001',3500')
insert into TEST1 values('002',700')
insert into TEST1 values('002',4000')表TEST2中的数据:
insert into TEST2 values('001',7000')
insert into TEST2 values('001',200')
insert into TEST2 values('001',500')
insert into TEST2 values('002',1700')
insert into TEST2 values('002',1200')请写出一个SQL查询出 TEST2 中 name 相同的 money1 和 减去 相同name 的 TEST1表中的 money2
如以上结果应该为:
select .......结果为:name result
001 1100
002 -1800
into temp test with no log;insert into test
select name,-money1 from test1
select name,sum(result) as result from test group by 1
select name, money2 as money from test2
union all
select name, money1*-1 as money from test1)
group by name
(select name,-money1 money1 from test1
union select name,money2 from test2) t group by name;
SQL> select * from test1;NAME MONEY1
-------------------- ---------------------------------------
001 3000
001 100
001 3500
002 700
002 4000SQL> select * from test2;NAME MONEY2
-------------------- ---------------------------------------
001 7000
001 -100
001 800
002 1700
002 1200select name, sum(money) from(
select name, money2 as money from test2
union all
select name, money1*-1 as money from test1)
group by name;SQL>
6 /NAME SUM(MONEY)
-------------------- ----------
001 1100
002 -1800Select name,sum(money1) from
(select name,-money1 money1 from test1
union select name,money2 from test2) t
group by name;
SQL> /NAME SUM(MONEY1)
-------------------- -----------
001 1200
002 -1800