如果条件唯一，则 MySQL 不同计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19672001/
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 distinct count if conditions unique
提问by Federico
I am trying to build a query that tells me how many distinct women and men there are in a given dataset. The person is identified by a number 'tel'. It is possible for the same 'tel' to appear multiple times, but that 'tel's gender should only be counted one time!
我正在尝试构建一个查询，该查询告诉我在给定数据集中有多少不同的女性和男性。此人由数字“电话”标识。同一个“电话”可能会出现多次，但“电话的性别”只能计算一次！
7136609221 - male
7136609222 - male
7136609223 - female
7136609228 - male
7136609222 - male
7136609223 - female
7136609221 - 男性
7136609222 - 男性
7136609223 - 女性
7136609228 - 男性
7136609222 - 男性
7136609223 - 女性
This example_dataset would yield the following.
Total unique gender count: 4
Total unique male count: 3
Total unique female count: 1
此 example_dataset 将产生以下内容。
唯一性别总数：4
唯一男性总数：3
唯一女性总数：1
My attempted query:
我尝试的查询：
SELECT COUNT(DISTINCT tel, gender) as gender_count,
COUNT(DISTINCT tel, gender = 'male') as man_count,
SUM(if(gender = 'female', 1, 0)) as woman_count
FROM example_dataset;
There's actually two attempts in there. COUNT(DISTINCT tel, gender = 'male') as man_countseems to just return the same as COUNT(DISTINCT tel, gender)-- it doesn't take into account the qualifier there. And the SUM(if(gender = 'female', 1, 0))counts all the female records, but is not filtered by DISTINCT tels.
实际上有两次尝试。COUNT(DISTINCT tel, gender = 'male') as man_count似乎只是返回相同COUNT(DISTINCT tel, gender)- 它没有考虑那里的限定符。并且SUM(if(gender = 'female', 1, 0))计数所有女性记录，但不会被 DISTINCT 电话过滤。
回答by sgeddes
Here's one option using a subquery with DISTINCT:
这是使用子查询的一种选择DISTINCT：
SELECT COUNT(*) gender_count,
SUM(IF(gender='male',1,0)) male_count,
SUM(IF(gender='female',1,0)) female_count
FROM (
SELECT DISTINCT tel, gender
FROM example_dataset
) t
This will also work if you don't want to use a subquery:
如果您不想使用子查询，这也将起作用：
SELECT COUNT(DISTINCT tel) gender_count,
COUNT(DISTINCT CASE WHEN gender = 'male' THEN tel END) male_count,
COUNT(DISTINCT CASE WHEN gender = 'female' THEN tel END) female_count
FROM example_dataset
回答by Abhay Sehgal
There is another solution similar to @segeddes's second solution
还有一个类似于@segeddes 的第二个解决方案的解决方案
Select COUNT(DISTINCT tel) as gender_count,
COUNT(DISTINCT IF(gender = "male", tel, NULL)) as male_count,
COUNT(DISTINCT IF(gender = "female", tel, NULL)) as female_count
FROM example_dataset
Explanation :
解释 ：
IF(gender = "male", tel, NULL)
Above expression will return tel if gender is male else it will return NULL value
如果性别是男性，上面的表达式将返回 tel 否则它将返回 NULL 值
Then we've
那我们就
DISTINCT
It will remove all the duplicates
它将删除所有重复项
And finally
最后
COUNT(DISTINCT IF(gender = "male", tel, NULL))
Will count all the distinct occurrences of rows having male gender
将计算所有不同出现的具有男性性别的行
Note : SQL COUNT function with expression only counts rows with non NULL values, for detailed explanation check - http://www.mysqltutorial.org/mysql-count/
注意：带有表达式的 SQL COUNT 函数只计算具有非 NULL 值的行，详细解释检查 - http://www.mysqltutorial.org/mysql-count/
