SQL 计数与条件不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14048098/
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
COUNT DISTINCT with CONDITIONS
提问by derekhh
I want to count the number of distinct items in a column subject to a certain condition, for example if the table is like this:
我想根据特定条件计算列中不同项目的数量，例如，如果表是这样的：
tag | entryID
----+---------
foo | 0
foo | 0
bar | 3
If I want to count the number of distinct tags as "tag count" and count the number of distinct tags with entry id > 0 as "positive tag count" in the same table, what should I do?
如果我想将不同标签的数量计算为“标签计数”，并将条目 id > 0 的不同标签的数量计算为同一个表中的“正标签计数”，我该怎么办？
I'm now counting from two different tables where in the second table I've only selected those rows with entryID larger than zero. I think there should be a more compact way to solve this problem.
我现在从两个不同的表中进行计数，其中在第二个表中我只选择了 entryID 大于零的那些行。我认为应该有更紧凑的方法来解决这个问题。
回答by ntalbs
You can try this:
你可以试试这个：
select
count(distinct tag) as tag_count,
count(distinct (case when entryId > 0 then tag end)) as positive_tag_count
from
your_table_name;
The first count(distinct...)is easy.
The second one, looks somewhat complex, is actually the same as the first one, except that you use case...whenclause. In the case...whenclause, you filter only positive values. Zeros or negative values would be evaluated as nulland won't be included in count.
第一个count(distinct...)很容易。第二个看起来有点复杂，实际上和第一个一样，只是你使用了case...when子句。在case...when子句中，您仅过滤正值。零或负值将被评估为null并且不会包含在计数中。
One thing to note here is that this can be done by reading the table once. When it seems that you have to read the same table twice or more, it can actually be done by reading once, in most of the time. As a result, it will finish the task a lot faster with less I/O.
这里要注意的一件事是，这可以通过阅读一次表格来完成。当您似乎必须两次或更多次阅读同一张表时，实际上可以通过阅读一次来完成，在大多数情况下。因此，它将以更少的 I/O 更快地完成任务。
回答by MJBLACKEND
Try the following statement:
试试下面的语句：
select distinct A.[Tag],
count(A.[Tag]) as TAG_COUNT,
(SELECT count(*) FROM [TagTbl] AS B WHERE A.[Tag]=B.[Tag] AND B.[ID]>0)
from [TagTbl] AS A GROUP BY A.[Tag]
The first field will be the tag the second will be the whole count the third will be the positive ones count.
第一个字段是标签，第二个字段是整个计数，第三个字段是正数。
回答by zaz
This may work:
这可能有效：
SELECT Count(tag) AS 'Tag Count'
FROM Table
GROUP BY tag
and
和
SELECT Count(tag) AS 'Negative Tag Count'
FROM Table
WHERE entryID > 0
GROUP BY tag
回答by BrianC
This may also work:
这也可能有效：
SELECT
COUNT(DISTINCT T.tag) as DistinctTag,
COUNT(DISTINCT T2.tag) as DistinctPositiveTag
FROM Table T
LEFT JOIN Table T2 ON T.tag = T2.tag AND T.entryID = T2.entryID AND T2.entryID > 0
You need the entryID condition in the left join rather than in a where clause in order to make sure that any items that only have a entryID of 0 get properly counted in the first DISTINCT.
您需要在左连接中而不是在 where 子句中使用 entryID 条件，以确保在第一个 DISTINCT 中正确计算 entryID 为 0 的任何项目。
回答by Abhishek Gupta
Code counts the unique/distinct combination of Tag & Entry ID when [Entry Id]>0
当 [Entry Id]>0 时，Code 统计 Tag 和 Entry ID 的唯一/不同组合
select count(distinct(concat(tag,entryId)))
from customers
where id>0
In the output it will display the count of unique values Hope this helps
在输出中，它将显示唯一值的计数希望这有帮助
