select name,sum(数量) from (select name ,数量 from 表1 union all select name ,-1*数量 from 表1 ) group by name
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
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
1.本人觉得此效率最好 select 品名,sum(数量) 库存 from (select 品名,数量 数量 from A union all select 品名,-数量 数量 from B) group by 品名
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
兄弟,,-1*数量 from 表1中的表1应改成表2吧,你写了个好的方法,但是要给我们学习的人容易理解啊
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
select 品名,sum(数量) 库存 from (select 品名,数量 数量 from A union all select 品名,-数量 数量 from B) group by 品名能执行吗? 我执行完后,报错:ORA-01790:expression must have same datatype as corresponding expression
如果数据有规律的话, 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)
from (select name ,数量 from 表1
union all
select name ,-1*数量 from 表1
)
group by name
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
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
select 品名,sum(数量) 库存
from (select 品名,数量 数量 from A
union all
select 品名,-数量 数量 from B)
group by 品名
from (select 品名,数量 数量 from A
union all
select 品名,-数量 数量 from B)
group by 品名能执行吗?
我执行完后,报错:ORA-01790:expression must have same datatype as corresponding expression
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)