英文:
How to sum total amount without grouping even have duplicated number?
问题
结果
20.0 VC10001
10.0 VC10003
----------------------------
可能显示结果如下吗？
结果
10.0 VC10001
10.0 VC10001
10.0 VC10003
希望能帮助到您。
英文:
table_a table_b
desc amount ID vcref ID
banana 2.00 101 VC10001 101
apple 3.00 101 VC10001 101
orange 5.00 101 VC10003 101
select sum(a.amount),b.vcref from table_a a,table_b b where a.ID=b.ID group by b.vcref;
result
20.0 VC10001
10.0 VC10003
-------------------------------------------
May I know it is possible to show result like this ?
result
10.0 VC10001
10.0 VC10001
10.0 VC10003
Anyone help is much appreciated.
答案1
得分: 1
选择作为内部查询的总和：
select
(select sum(amount) from table_a) as 结果,
vcref
from table_b
英文:
Select the sum as an inner query:
select
(select sum(amount) from table_a) as result,
vcref
from table_b
答案2
得分: 1
使用相关子查询的一种选择是：
示例数据：
SQL> with
2 table_a (c_desc, amount, id) as
3 (select 'banana', 2, 101 from dual union all
4 select 'apple' , 3, 101 from dual union all
5 select 'orange', 5, 101 from dual
6 ),
7 table_b (vcref, id) as
8 (select 'vc1001', 101 from dual union all
9 select 'vc1001', 101 from dual union all
10 select 'vc1003', 101 from dual
11 )
查询：
12 select b.vcref,
13 (select sum(a.amount) from table_a a where a.id = b.id) total
14 from table_b b;
VCREF TOTAL
------ ----------
vc1001 10
vc1001 10
vc1003 10
SQL>;
英文:
One option is to use a correlated subquery:
Sample data:
SQL> with
2 table_a (c_desc, amount, id) as
3 (select 'banana', 2, 101 from dual union all
4 select 'apple' , 3, 101 from dual union all
5 select 'orange', 5, 101 from dual
6 ),
7 table_b (vcref, id) as
8 (select 'vc1001', 101 from dual union all
9 select 'vc1001', 101 from dual union all
10 select 'vc1003', 101 from dual
11 )
Query:
12 select b.vcref,
13 (select sum(a.amount) from table_a a where a.id = b.id) total
14 from table_b b;
VCREF TOTAL
------ ----------
vc1001 10
vc1001 10
vc1003 10
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论