MySQL 计算不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4688814/
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 values
提问by willlangford
I have a data set asking a customer how many pets they have for example. Is there a way with one query I can count the distinct values (1,2,3, etc)? Thanks!
例如，我有一个数据集询问客户他们有多少宠物。有没有一种方法可以通过一个查询来计算不同的值（1、2、3 等）？谢谢！
+----------+------+
| Customer | Pets |
+----------+------+
| 20 | 2 |
| 21 | 3 |
| 22 | 3 |
| 23 | 2 |
| 24 | 4 |
+----------+------+
What I want is a list saying:
我想要的是一个列表，上面写着：
- 2 had 2 Pets
- 2 had 3 Pets
- 1 had 4 Pets
- 2 有 2 个宠物
- 2 有 3 宠物
- 1 有 4 宠物
回答by Fibericon
You can do a distinct count as follows:
您可以按如下方式进行非重复计数：
SELECT COUNT(DISTINCT column_name) FROM table_name;
EDIT:
编辑：
Following your clarification and update to the question, I see now that it's quite a different question than we'd originally thought. "DISTINCT" has special meaning in SQL. If I understand correctly, you want something like this:
在您对问题进行澄清和更新之后，我现在看到这是一个与我们最初想象的完全不同的问题。“DISTINCT”在 SQL 中有特殊含义。如果我理解正确，你想要这样的东西：
- 2 customers had 1 pets
- 3 customers had 2 pets
- 1 customers had 3 pets
- 2 位顾客有 1 只宠物
- 3 位顾客养了 2 只宠物
- 1 位顾客养了 3 只宠物
Now you're probably going to want to use a subquery:
现在您可能想要使用子查询：
select COUNT(*) column_name FROM (SELECT DISTINCT column_name);
Let me know if this isn't quite what you're looking for.
如果这不是您要查找的内容，请告诉我。
回答by maid450
Ok, I deleted my previous answer because finally it was not what willlangford was looking for, but I made my point that maybe we were all misunderstanding the question.
好的，我删除了我之前的答案，因为最终这不是 Willlangford 想要的，但我指出，也许我们都误解了这个问题。
I also thought of the SELECT DISTINCT...thing at first, but it seemed too weird to me that someone needed to know how many people had a different number of pets than the rest... thats why I thought that maybe the question was not clear enough.
一开始我也想到了这个SELECT DISTINCT...问题，但对我来说似乎太奇怪了，有人需要知道有多少人拥有的宠物数量与其他人不同……这就是为什么我认为这个问题可能不够清楚。
So, now that the real question meaning is clarified, making a subquery for this its quite an overhead, I would preferably use a GROUP BYclause.
所以，既然真正的问题含义已经明确，为此创建一个子查询会带来相当大的开销，我最好使用一个GROUP BY子句。
Imagine you have the table customer_petslike this:
想象一下你有这样的表customer_pets：
+-----------------------+
| customer | pets |
+------------+----------+
| customer1 | 2 |
| customer2 | 3 |
| customer3 | 2 |
| customer4 | 2 |
| customer5 | 3 |
| customer6 | 4 |
+------------+----------+
then
然后
SELECT count(customer) AS num_customers, pets FROM customer_pets GROUP BY pets
would return:
会返回：
+----------------------------+
| num_customers | pets |
+-----------------+----------+
| 3 | 2 |
| 2 | 3 |
| 1 | 4 |
+-----------------+----------+
as you need.
根据您的需要。
回答by hayatbiralem
I think this linkis pretty good.
我觉得这个链接很好。
Sample output from that link:
该链接的示例输出：
mysql> SELECT cate_id,COUNT(DISTINCT(pub_lang)), ROUND(AVG(no_page),2)
-> FROM book_mast
-> GROUP BY cate_id;
+---------+---------------------------+-----------------------+
| cate_id | COUNT(DISTINCT(pub_lang)) | ROUND(AVG(no_page),2) |
+---------+---------------------------+-----------------------+
| CA001 | 2 | 264.33 |
| CA002 | 1 | 433.33 |
| CA003 | 2 | 256.67 |
| CA004 | 3 | 246.67 |
| CA005 | 3 | 245.75 |
+---------+---------------------------+-----------------------+
5 rows in set (0.00 sec)
回答by VSJ
You can use this:
你可以使用这个：
select count(customer) as count, pets
from table
group by pets
回答by A. Sharma
SELECT CUSTOMER, COUNT(*) as PETS
FROM table_name
GROUP BY CUSTOMER;
