将分组应用于If语句结果

Essentially, I have two tables:
Table A

基本上，我有两个表：表A.

aId|isOne|bId
---+-----+---
1  |1    |2
2  |0    |2
3  |1    |1

Table B

表B.

bId|one|two
---+---+---
1  |5  |13
2  |3  |11

Table A refers to Table B and specifies whether the data of one or of two is desired. What I need to do is sum the values given the bId, so the expected result of the query on these table would be:

表A参考表B并指定是否需要一个或两个的数据。我需要做的是对给定bId的值求和，因此这些表上的查询的预期结果将是：

bId|value
---+-----
2  |14
1  |5

Currently my query for doing this has the following form:

目前，我执行此操作的查询具有以下形式：

select bId, 
        coalesce(if(bId = 0, null, 
                    sum(if(isOne = 0, 
                           (select two from tableB where tableB.bId = bId),
                           (select one from tableB where tableB.bId = bId)))) as value 
from (select bId, isOne, one, two from tableA 
      join tableB on tableA.bId = tableB.bId) as tableRes;

Please note that this is in relaity part of a larger query and uses larger tables, where the coalesce and first if statement do make sense to use.

请注意，这是较大查询的相关部分，并使用较大的表，其中coalesce和first if语句有意义使用。

An error occurs with the above query though (

但上述查询发生错误（

Error Code: 1242. Subquery returns more than 1 row)

错误代码：1242。子查询返回超过1行）

which is believed to come from the if statement within sum(). I have tried applying group by, since I think I need to group the bId = 2 value results together, but have failed to be able to place this legally, or place it legally within the query and have it actually stop the subqueries from returning more than one result. Any direction or help in fixing the error would be appreciated, also may good to know that as stated, this is a stripped down version of the query, so if it is thought the problem is not in what has been shown more can be added, but I'm pretty confident in the error lying in the above version.

这被认为来自sum（）中的if语句。我尝试过应用group by，因为我认为我需要将bId = 2值结果组合在一起，但是无法合法地放置它，或者将它合法地置于查询中并让它实际上阻止子查询返回更多不止一个结果。任何修复错误的方向或帮助都会受到赞赏，也可以很好地知道，如上所述，这是查询的精简版本，所以如果认为问题不是已经显示的内容可以添加更多，但我对上述版本中的错误非常有信心。

3 个解决方案

#1

I would simply inner join the 2 tables and use case statement within the sum() to sum the right values:

我只需要内部连接2个表并在sum（）中使用case语句来总结正确的值：

select tableA.bId, sum(case when isOne=0 then two when isOne=1 then one else 0 end) as val
from tableA 
inner join tableB on tableA.bId = tableB.bId
group by tableA.bId ;

#2

You don't need such a complicated structure, just do the sums in the join.

你不需要这么复杂的结构，只需要在连接中做总和。

SELECT tableA.bId, SUM(IF(isOne, one, two)) AS value
FROM tableA
JOIN tableB ON tableA.bId = tableB.bId
GROUP BY tableA.bId

#3

I was actually able to solve this by simply replacing the arbitrary bId within the subqueries with a specified value of tableA.bId, for anyone curious of an answer.

我实际上能够通过简单地用指定的tableA.bId值替换子查询中的任意bId来解决这个问题，对于任何好奇的答案。

#1