关于两张表之间查询

[xingyuanxingchen]

表一库存
A、990
B、1200

表二取出
A、99
A、89
B、77
B、109
查询两张表之间A 和 B 之间剩余多少库存？
请问大家该怎么写？

[iqlife]

select name,sum(数量)
from (select name ,数量 from 表1
union all
select name ,-1*数量 from 表1
)
group by name

[Phoenix_99]

with A as(
select 'A' a,990 b from dual
union all
select 'B' a,1200 b from dual
),B as(
select 'A' a,99 c from dual
union all
select 'A' a,89 c from dual
union all
select 'B' a,77 c from dual
union all
select 'B' a,109 c from dual
)
select a.a,b-sum(c) from a,b where a.a = b.a group by a.a,b

[xdy3008]

PLSQL 下测试：

with table1 as (
select 'A' as id, 990 as total from dual union all
select 'B' as id, 1200 as total from dual 
),
table2 as 
(
select 'A' as id, 99 as consume from dual  union all
select 'A' as id, 89 as consume from dual  union all
select 'B' as id, 77 as consume from dual  union all
select 'B' as id, 109 as consume from dual 
)

select a.id, total - consume as remain from
(
       select id, sum(total) total  from table1  group by id 
) a
inner join 
(
      select id, sum(consume) consume from table2  group by id 
) b on a.id = b.id 

-----------
A	802
B	1014

[心中的彩虹]

[Quote=引用楼主 xingyuanxingchen 的回复:]
表一库存
A、990
B、1200

表二取出
A、99
A、89
B、77
B、109
查询两张表之间A 和 B 之间剩余多少库存？
请问大家该怎么写？
[/Quote]

1.本人觉得此效率最好
select 品名,sum(数量) 库存
from (select 品名,数量 数量 from A 
union all
select 品名,-数量 数量 from B)
group by 品名

[lzbbob1985]

[Quote=引用 4 楼 wkc168 的回复:]
引用楼主 xingyuanxingchen 的回复:
表一库存
A、990
B、1200

表二取出
A、99
A、89
B、77
B、109
查询两张表之间A 和 B 之间剩余多少库存？
请问大家该怎么写？



SQL code

1.本人觉得此效率最好
select 品名,sum(数量) 库存
from (select 品名,数量 数量 from A
……
[/Quote]
+1

[AIHUASHENG]

二楼回答的最好，我就不说了

[vitahuang]

二楼同学的方法不错！up

[huangdh12]

select a.col1,(a.col2-c.count2) from table_a a,(select sum(col2) count2 ,col1 from table_b b group by b.col1) c where a.col1 = c.col1

[WXHDSWH]

[Quote=引用 1 楼 java3344520 的回复:]
SQL code
select name,sum(数量)
from (select name ,数量 from 表1
union all
select name ,-1*数量 from 表1
)
group by name
[/Quote]
兄弟，,-1*数量 from 表1中的表1应改成表2吧，你写了个好的方法，但是要给我们学习的人容易理解啊

[璇之星]

[Quote=引用 1 楼 java3344520 的回复:]
SQL code
select name,sum(数量)
from (select name ,数量 from 表1
union all
select name ,-1*数量 from 表1
)
group by name
[/Quote]
up！！！！！

[goalboy118]

select a.col1,(a.col2-c.count2) from table_a a,(select sum(col2) count2 ,col1 from table_b b group by b.col1) c where a.col1 = c.col1

[lianruidong]

顶，学习了

[dx286123549]

学习了。。

[zlhxxp]

select 品名,sum(数量) 库存
from (select 品名,数量 数量 from A
union all
select 品名,-数量 数量 from B)
group by 品名

能执行吗？
我执行完后，报错：ORA-01790:expression must have same datatype as corresponding expression

[qykong1986]

如果数据有规律的话，
with a as
(
select 1 id,'zhangsan' a,97 b,93 c from dual
union all
select 2,'zhangsan',93,92 from dual
union all
select 3,'zhangsan',80,0 from dual
union all
select 4,'zhangsan',97,0 from dual
)
select a.id, a.a name, a.b Chinese,a.b Math, b.b Eng
from a , a b
where a.id=(b.id-2)