SQL 如何在 Oracle 中执行 countIf()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40087843/
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
How to do countIf() in Oracle
提问by amsbam1
How do I select a variable which gives an output the same as the excel function:
如何选择一个变量，其输出与 excel 函数相同：
COUNTIFS(A1:D1,"<25", A1:D1, ">16")?
I.e. to count the number of times the values in my four fields are between 16 and 25.
即计算我的四个字段中的值在 16 到 25 之间的次数。
采纳答案by mathguy
You can do this with count()and caseexpressions. Note: casehas an optional elseclause; if it is not used, then the default else"value" is null. Count only counts non-null values, so you can combine these observations to write compact code. WHAT is counted doesn't matter; so the caseexpression may return a number (1 is usual, but 0 is just as valid and will give the same result, since the values are COUNTED, not SUMMED) - but you could also have a string like 'x' or a date in the case expression. Just to illustrate that, I will use a string.
你可以用count()和case表达式来做到这一点。注意：case有一个可选的else子句；如果未使用，则默认else“值”为null. Count 只计算非空值，因此您可以结合这些观察结果来编写紧凑的代码。计算什么并不重要；所以case表达式可能会返回一个数字（1 是常见的，但 0 也同样有效，并且会给出相同的结果，因为这些值是 COUNTED，而不是 SUMMED） - 但你也可以有一个像 'x' 或日期的字符串案例表达。为了说明这一点，我将使用一个字符串。
select count( case when col > 16 and col < 25 then 'x' end ) as ct from your_table;
Here your_tableis the name of your table, colis the name of the columncontaining the values, and ctis the name of the resulting column (the label for the count of values that satisfy your condition).
这your_table是表col的名称，是包含值的列ct的名称，是结果列的名称（满足条件的值计数的标签）。
Of course, in a database you can get the same result more easily:
当然，在数据库中，您可以更轻松地获得相同的结果：
select count(*) as ct from your_table where col > 16 and col < 25;
Note, though, that the values are in one column.
但请注意，这些值位于一列中。
If instead you have a table with four COLUMNS and many rows, and all the values in all columns are numbers, and you want to add a column showing how many values are strictly between 16 and 25 IN EACH ROW, the solution is different (but it uses the same ideas):
相反，如果您有一个包含四个 COLUMNS 和许多行的表，并且所有列中的所有值都是数字，并且您想要添加一列来显示在 16 到 25 IN EACH ROW 之间有多少个值，则解决方案是不同的（但是它使用相同的想法）：
select col1, col2, col3, col4,
case when col1 > 16 and col1 < 25 then 1 else 0 end +
case when col2 > 16 and col2 < 25 then 1 else 0 end +
case when col3 > 16 and col3 < 25 then 1 else 0 end +
case when col4 > 16 and col4 < 25 then 1 else 0 end as ct
from my_table;
回答by Gordon Linoff
You would do this in a SQL query using case:
您可以在 SQL 查询中使用case：
select sum(case when col between 16 and 25 then 1 else 0 end)
from t;
Note that betweenis inclusive in SQL, not exclusive, so based on your code logic:
请注意，between它包含在 SQL 中，而不是排他的，因此基于您的代码逻辑：
select sum(case when col > 16 and col < 25 then 1 else 0 end)
from t;
回答by Mr. Mak
Think, you have a 'user_table', where some of the user's 'status'are active (code-11)and others are inactive (code-22). You can count active and inactive with this sqlbelow.
想想，你有一个'user_table'，其中一些用户的“状态”是 ，active (code-11)而另一些是inactive (code-22)。您可以sql在下面计算活动和非活动。
select count(case when status = 11 then 1 end) u_active, count(case when status = 22 then 1 end) u_inactive, from user_table;
select count(case when status = 11 then 1 end) u_active, count(case when status = 22 then 1 end) u_inactive, from user_table;
