英文:
How to determine the sum for a table row?
问题
Here's the translation of your content:
给定
我有两个类似于以下示例的表格：
Table1
| ID1 | ID2 | ID3 | ID4 |
|---|---|---|---|
| first | second | third | fourth |
| fifth | sixth | seventh | eighth |
Table2
| ID | Sum |
|---|---|
| first | 1 |
| third | 5 |
| fourth | 4 |
| fifth | 1 |
| seventh | 5 |
| second | 4 |
| eighth | 1 |
| sixth | 2 |
预期
我需要根据Table2中的值之和，获取Table1的每一行的总和。
例如，Table1的第一行的总和为14。
因为 first (1) + second (4) + third (5) + fourth (4) = 14
结果表格：
| ID1 | ID2 | ID3 | ID4 | 总和 |
|---|---|---|---|---|
| first | second | third | fourth | 14 |
| fifth | sixth | seventh | eighth | 9 |
如何通过SQL获取每个表格行的总和？
英文:
Given
I have two tables similar to following example:
Table1
| ID1 | ID2 | ID3 | ID4 |
|---|---|---|---|
| first | second | third | fourth |
| fifth | sixth | seventh | eighth |
Table2
| ID | Sum |
|---|---|
| first | 1 |
| third | 5 |
| fourth | 4 |
| fifth | 1 |
| seventh | 5 |
| second | 4 |
| eighth | 1 |
| sixth | 2 |
Expected
I need to get the total sum for each row of Table1 based on the sum of the values from Table2.
For example, the first row of Table1 becomes added the sum 14.
Because first (1) + second (4) + third (5) + fourth (4) = 14
Resulting table:
| ID1 | ID2 | ID3 | ID4 | Sum |
|---|---|---|---|---|
| first | second | third | fourth | 14 |
| fifth | sixth | seventh | eighth | 9 |
How can I get the sum for each of those table rows by SQL ?
答案1
得分: 0
将Table2连接四次：
SELECT t1.*, t2_1.Sum + t2_2.Sum + t2_3.Sum + t2_4.Sum As Sum
FROM Table1 t1
INNER JOIN Table2 t2_1 ON t2_1.ID = t1.ID1
INNER JOIN Table2 t2_2 ON t2_2.ID = t1.ID2
INNER JOIN Table2 t2_3 ON t2_3.ID = t1.ID3
INNER JOIN Table2 t2_4 ON t2_4.ID = t1.ID4
英文:
Join to Table2 four separate times:
SELECT t1.*, t2_1.Sum + t2_2.Sum + t2_3.Sum + t2_4.Sum As Sum
FROM Table1 t1
INNER JOIN Table2 t2_1 ON t2_1.ID = t1.ID1
INNER JOIN Table2 t2_2 ON t2_2.ID = t1.ID2
INNER JOIN Table2 t2_3 ON t2_3.ID = t1.ID3
INNER JOIN Table2 t2_4 ON t2_4.ID = t1.ID4
答案2
得分: 0
你可以使用以下代码将Table2与条件为ID在Table1的ID之一中的内容连接起来，然后使用group by和sum来获得所需的输出结果：
SELECT t1.*, SUM(t2.Sum) AS Sum
FROM Table1 t1
JOIN Table2 t2 ON
t2.ID IN (t1.ID1, t1.ID2, t1.ID3, t1.ID4)
GROUP BY t1.ID1, t1.ID2, t1.ID3, t1.ID4;
英文:
You could join Table2 with the condition being that ID is in one of the Table1 IDs. Then using group by and sum get your desired output:
SELECT t1.*, SUM(t2.Sum) AS Sum
FROM Table1 t1
JOIN Table2 t2 ON
t2.ID IN (t1.ID1, t1.ID2, t1.ID3, t1.ID4)
GROUP BY t1.ID1, t1.ID2, t1.ID3, t1.ID4;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论