将一行与多个行进行比较后的总和。

I have two tables that will be a and b. I want to compare number in a to number in b where they share an ID. a has a single row, while b has multiple. If the sum of b is less than a, then that row of a should be added to the total sum, otherwise it should be skipped.

我有两个表，分别是a和b，我想把a和b的数字比较一下，它们共享一个ida, a只有一行，而b有多个。如果b的和小于a，那么a的那一行应该加到总数中，否则应该跳过。

Table a
ID  Number
4   50
5   60
6   70

Table b
ID  Number  SharedID
1   30      4
2   25      4
3   50      5
4   5       5
5   30      6
6   10      6

Using that example: b 1 and 2 are greater than a 4, so it wouldn't be counted. b 3 and 4 are less than a 5 so it would count. b 5 and 6 are less than a 6, so it would count. The total should be 130.

使用这个例子:b1和2比4大，所以不会被计算。b3和4小于5，所以它是有意义的。b 5和6小于6，所以它是有意义的。总数应该是130。

I'm having trouble with doing a comparison of one row to multiple and then only summing some of the numbers.

我在对一行与多行进行比较时遇到了麻烦，然后只对一些数字求和。

3 个解决方案

#1

This should do the job:

这应该可以做到:

select sum(Number)
from a
where Number > (select sum(Number) from b where b.SharedID = a.ID)

#2

Try this query:

试试这个查询:

SELECT SUM(a.Number)
FROM a INNER JOIN
(
    SELECT b.SharedID, SUM(b.Number) AS theSum
    FROM b
    GROUP BY b.SharedID
) t
ON a.ID = t.SharedID
WHERE t.theSum < a.Number

The conceptually easiest option is to create a temporary table containing the sums of Table b, and then to JOIN that back to Table a. The WHERE clause restricts your total to only a Number values which are greater than the b sum.

概念上最简单的选项是创建一个包含表b和的临时表，然后将其连接回表a。

#3

select SUM(number) from(
select case when(aa.number>(select SUM(bb.number)
 from bb where bb.sharedid=aa.id))then aa.number else 0 end as number  from aa )abc

This should do the trick

这应该能达到目的

#1