MySql：是否可以“SUM IF”或“COUNT IF”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13075505/
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
MySql: is it possible to 'SUM IF' or to 'COUNT IF'?
提问by realtebo
I have a column 'hour' I have a column 'kind' (it can be 1,2 or 3)
我有一列“小时”我有一列“种类”（它可以是 1,2 或 3）
I'd like to do something like:
我想做类似的事情：
SELECT count(id), SUM(hour) as totHour, SUM( IF ( kind = 1, 1, 0 ) ) as countKindOne
or
或者
SELECT count(id), SUM(hour) as totHour, COUNT( IF ( kind = 1 ) ) as countKindOne
But mysql tell me I've an error... what's the error!?
但是 mysql 告诉我我有一个错误......错误是什么！？
Please see this stackoverflow topic: MySQL SUM IF field b = field a
请参阅此 stackoverflow 主题：MySQL SUM IF field b = field a
.. I'm not able to reply this ...
..我无法回复这个......
回答by Taryn
You can use a CASEstatement:
您可以使用以下CASE语句：
SELECT count(id),
SUM(hour) as totHour,
SUM(case when kind = 1 then 1 else 0 end) as countKindOne
回答by Gavin Towey
you want something like:
你想要这样的东西：
SELECT count(id), SUM(hour) as totHour, SUM(kind=1) as countKindOne;
Note that your second example was close, but the IF() function alwaystakes three arguments, so it would have had to be COUNT(IF(kind=1,1,NULL)). I prefer the SUM() syntax shown above because it's concise.
请注意，您的第二个示例很接近，但是 IF() 函数始终采用三个参数，因此它必须是COUNT(IF(kind=1,1,NULL)). 我更喜欢上面显示的 SUM() 语法，因为它很简洁。
回答by Onur Y?ld?r?m
You can also use SUM + IFwhich is shorter than SUM + CASE:
您还可以使用SUM + IF短于SUM + CASE：
SELECT
count(id)
, SUM(IF(kind=1, 1, 0)) AS countKindOne
, SUM(CASE WHEN kind=2 THEN 1 ELSE 0 END) AS countKindTwo
回答by eci
There is a slight difference between the top answers, namely SUM(case when kind = 1 then 1 else 0 end)and SUM(kind=1).
顶级答案之间略有不同，即SUM(case when kind = 1 then 1 else 0 end)和SUM(kind=1)。
When all values in column kindhappen to be NULL, the result of SUM(case when kind = 1 then 1 else 0 end)is 0, whereas the result of SUM(kind=1)is NULL.
当列中的所有值kind恰好为 时NULL，结果SUM(case when kind = 1 then 1 else 0 end)为0，而结果SUM(kind=1)为NULL。
An example (http://sqlfiddle.com/#!9/b23807/2):
一个例子（http://sqlfiddle.com/#!9/b23807/2）：
Schema:
架构：
CREATE TABLE Table1
(`first_col` int, `second_col` int)
;
INSERT INTO Table1
(`first_col`, `second_col`)
VALUES
(1, NULL),
(1, NULL),
(NULL, NULL)
;
Query results:
查询结果：
SELECT SUM(first_col=1) FROM Table1;
-- Result: 2
SELECT SUM(first_col=2) FROM Table1;
-- Result: 0
SELECT SUM(second_col=1) FROM Table1;
-- Result: NULL
SELECT SUM(CASE WHEN second_col=1 THEN 1 ELSE 0 END) FROM Table1;
-- Result: 0
回答by Fernando Souza
From MYSQL I solved the problem like this:
从 MYSQL 我解决了这样的问题：
SUM(CASE WHEN used = 1 THEN 1 ELSE 0 END) as amount_one,
Hope this helps :D
希望这有帮助：D
回答by Antony
It is worth noting that you can build upon Gavin Toweys answer by using multiple fields from across your query such as
值得注意的是，您可以通过使用跨查询的多个字段来构建 Gavin Toweys 的答案，例如
SUM(table.field = 1 AND table2.field = 2)
SUM(table.field = 1 AND table2.field = 2)
You can also use this syntax for COUNTand I am sure other functions as well.
您也可以使用此语法COUNT，我相信其他功能也是如此。
