MySQL SELECT CASE SQL 中的 DISTINCT COUNT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10398010/
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
DISTINCT COUNT in SELECT CASE SQL
提问by Steve Helgeson
I have a table of reports that include the fields Case(unique number), ISR(Individual Safety Report - unique number) and YearsOld.
我有一个报告表，其中包括字段Case（唯一编号）、ISR（个人安全报告 - 唯一编号）和YearsOld.
There can be more than one ISR for each Case. I want to count the number of unique Cases within age groups.
每个案例可以有多个 ISR。我想计算年龄组内独特案例的数量。
This SQL gives me a count of the number of ISRs:
这个 SQL 给了我一个 ISR 数量的计数：
SELECT
COUNT(CASE WHEN `YearsOld` = -2) THEN 1 END) `No Report`,
COUNT(CASE WHEN `YearsOld` BETWEEN 0 AND 5) THEN 1 END) `0 to 5`
COUNT(CASE WHEN `YearsOld` BETWEEN 6 AND 12) THEN 1 END) `6 to 12`
FROM `Demographics`
is there a way to modify this to count the DISTINCT Casesfor these Age Groups?
有没有办法修改它来计算Cases这些年龄组的 DISTINCT ？
回答by davesnitty
If your "case" variable is unique, you can certainly put the distinct keyword in the SQL CASE syntax directly:
如果您的“case”变量是唯一的，您当然可以直接将 distinct 关键字放在 SQL CASE 语法中：
Count(distinct CASE when yearsold between 6 and 12 then case else null end)
That way, each unique value of the case variable is counted only once.
这样，case 变量的每个唯一值只计算一次。
Just a note on column naming, I would suggest not using a word that has meaning in SQL if you have a choice (I.e. use 'case_num' instead of case).
只是关于列命名的说明，如果您有选择，我建议不要使用在 SQL 中有意义的词（即使用“case_num”而不是 case）。
回答by eggyal
You could use a subquery to filter your demographics table for a single YearsOldfield per case, although if that case might have been related to difference ages for different ISRit'll only end up being counted in one bracket (perhaps this is what you want?):
您可以使用子查询来过滤YearsOld每个案例的单个字段的人口统计表，尽管如果该案例可能与不同年龄的差异有关，ISR它最终只会被计入一个括号中（也许这就是您想要的？） ：
SELECT
... -- as you currently have
FROM (
SELECT `Case`, `YearsOld` from `Demographics` GROUP BY `Case`
) t;
Alternatively, to "count" each "distinct" "case" within each bracket, you do literally that:
或者，要“计算”每个括号内的每个“不同”“案例”，您可以按字面意思这样做：
SELECT
COUNT(DISTINCT CASE WHEN `YearsOld` = -2 THEN 1 END) `No Report`,
COUNT(DISTINCT CASE WHEN `YearsOld` BETWEEN 0 AND 5 THEN `Case` END) `0 to 5`,
COUNT(DISTINCT CASE WHEN `YearsOld` BETWEEN 6 AND 12 THEN `Case` END) `6 to 12`
FROM Demographics;
