使用以下输出连接两个表的SQL查询

I need to write a simple query as a part of larger function to join two tables. The tables are as under

我需要编写一个简单的查询作为连接两个表的较大函数的一部分。表格如下

Table1

Code    Subactivity
647     1
647     2
648     3
648     4

Table 2

Subactivity    Hours
1              5
2              10
3              7
4              3

The final output should look like

最终输出应该是这样的

Code    hours
647     15
648     10

I have done this before, but today I just cant get my head around it..

我以前做过这个,但今天我不能理解它...

1 个解决方案

#1

DECLARE @t1 TABLE([Code] INT,[Subactivity] INT)
INSERT INTO @t1 VALUES(647,1),(647,2),(648,3),(648,4)

DECLARE @t2 TABLE([Subactivity] INT, [Hours] INT)
INSERT INTO @t2 VALUES(1,5),(2,10),(3,7),(4,3)

SELECT t1.Code,SUM(t2.Hours) hours
FROM @t1 t1 JOIN @t2 t2 ON t1.subactivity = t2.subactivity
GROUP BY t1.CODE 

Result

Code    hours
647      15
648      10