MySQL 单行的总和值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2179207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum values of a single row?
提问by user151841
I have a MySQL query that returns a single row that is a series of 1s and 0s. It's for a progress bar indicator. I have the summing of it in code now, but I tried to sum the values in a query, and realized I couldn't use SUM(), because they're many columns but just one row.
我有一个 MySQL 查询，它返回一个由 1 和 0 组成的单行。它用于进度条指示器。我现在在代码中对它求和，但我试图对查询中的值求和，并意识到我不能使用 SUM()，因为它们有很多列但只有一行。
Is there a way I can sum this automatically in the query? It's like this:
有没有办法可以在查询中自动求和？就像这样：
item_1 | item_2 | item_3 | item_4
-------+--------+--------+--------
1 | 1 | 0 | 0
Edit: I forgot to mention, item_1and so forth are not simple field values, but each is rather an expression, such as SELECT IF( field_1 = 1 and field_2 IS NOT NULL, 0, 1 ) AS item_1 ..., so it looks like I have to do a nested query:
编辑：我忘了提及，item_1等等不是简单的字段值，但每个都是一个表达式，例如SELECT IF( field_1 = 1 and field_2 IS NOT NULL, 0, 1 ) AS item_1 ...，所以看起来我必须执行嵌套查询：
SELECT ( item_1 + item_2 ... ) FROM ( SELECT IF( field_1 = y and field_2 IS NOT NULL, 1, 0 ) AS item_1 ... ) AS alias
Correct?
正确的？
回答by RedFilter
select item_1 + item_2 + item_3 + item_4 as ItemSum
from MyTable
If there can be null values, you'll need to handle them like this:
如果可以有空值，您需要像这样处理它们：
select ifnull(item_1, 0) + ifnull(item_2, 0) + ifnull(item_3, 0) + ifnull(item_4, 0) as ItemSum
from MyTable
回答by Mark Byers
You can't really do it any simpler than:
你真的不能比以下更简单：
SELECT item_1 + item_2 + item_3 + item_4
FROM Table1
If you have a lot of columns then it will take a while to type in. I guess it took quite a long time to create the table too.
如果你有很多列，那么输入需要一段时间。我想创建表格也需要很长时间。
In future design your tables differently so that each stage of the process is a row not a column. In other words, instead of this:
将来以不同的方式设计您的表格，以便流程的每个阶段都是一行而不是一列。换句话说，而不是这样：
id, item_1, item_2, item_3, item_4
Your table could look like this:
您的表格可能如下所示：
id, item_number, value
And then you can query it like this:
然后你可以像这样查询它：
SELECT SUM(value)
FROM Table1
WHERE id = @id
In fact, you probably don't even need the value column. The presence or absence of the row is enough information. So your query becomes:
事实上，您可能甚至不需要 value 列。该行的存在或不存在是足够的信息。所以你的查询变成：
SELECT COUNT(*)
FROM Table1
WHERE id = @id
It's perhaps a bit late for you this time, but if you still have a chance to change the design, then it might be worth considering this.
这次对你来说可能有点晚了，但如果你还有机会改变设计，那么可能值得考虑一下。
