可以把这两张表连起来吗?

Question

我有两个表

表答:

+------------+----------+
| Entry From | Entry To |
+------------+----------+
|        100 |      103 |
|        104 |      105 |
|        106 |      109 |
+------------+----------+

表B:

+-------+-------+
| Entry | Value |
+-------+-------+
|   100 |    10 |
|   101 |     3 |
|   102 |     7 |
|   103 |     2 |
|   104 |     9 |
|   105 |    17 |
|   106 |     3 |
|   107 |     3 |
|   108 |     6 |
|   109 |     5 |
+-------+-------+

预期的结果:

+------------+----------+-------------+
| Entry From | Entry To | Total Value |
+------------+----------+-------------+
|        100 |      103 |          22 |
|        104 |      105 |          26 |
|        106 |      109 |          17 |
+------------+----------+-------------+

欢迎提出任何解决方案/建议。

感谢任何提前帮助!

Answer 1

请尝试:

Select 
    a.EntryFrom, a.EntryTo, sum(Value) TotalValue 
From TableA a INNER JOIN TableB b ON b.Entry between a.EntryFrom and a.EntryTo 
Group by a.EntryFrom, a.EntryTo

Answer 2

您要查找的可能是子查询。

SELECT 
    A.Entry_From, A.Entry_To, 
    (SELECT SUM(B.Value) FROM B 
        WHERE B.Entry BETWEEN A.Entry_From AND A.Entry_To) AS Total_Value
    FROM A

这也取决于SQL的版本，所以YMMV:)

Answer 3

这是一个工作小提琴:http://www.sqlfiddle.com/#!2/afbac/2使用这个查询:

select a.idxFrom, a.idxTo, sum(b.value) as total
from a inner join b on b.idx >= a.idxFrom and b.idx <= a.idxTo
group by a.idxFrom, a.idxTo