sql - SQL查询使用另一个表中的相同ID从列中求和值

Question

我有两个如下表；

table1
===========================
| table1_ID | table1_name |
===========================
|     1     |      A      |
|     2     |      B      |
===========================

table2
======================================
| table2_ID | table2_qty | table2_ID |
======================================
|     22    |      4     |     A     |
|     23    |      9     |     A     |
|     24    |     12     |     B     |
|     25    |     23     |     B     |
======================================

并且输出应该是这样的：

================================
| table1_ID | name | total_qty |
================================
|     1     |   A  |     13    |
|     2     |   B  |     35    |
================================

“table2 ID，名称和与'table1_ID'相同ID的'table2_qty'的总和值”

我试过这个，但结果不是我想要的。

SELECT table1.table1_ID, table1.table1_name,            
SUM(table2.table2_qty) As total_qty 
FROM table1, table2 
GROUP BY table1.table1_ID, table1.table1_name;

如何在 SQL 中获得该结果？
谢谢！

标签： sqlms-accesssuminner-joinaggregate-functions

Answer 1

这是一个使用相关子查询的选项：

select t1.*,
    (
        select sum(t2.table2_qty)
        from table2 as t2
        where t2.table2_id = t1.table1_name
    ) as total_qty
from table1 as t1

您还可以join汇总：

select t1.table1_id, t1.table1_name, sum(t2.table2_qty) as total_qty
from table1 t1
left join table2 t2 on t2.table2_id = t1.table1_name
group by t1.table1_id, t1.table1_name