MySQL计算唯一值和重复值 [英] MySQL count unique and duplicate values

问题描述

在计算MySQL的唯一值和重复值时，我需要一些帮助.我想知道总共有多少条记录，还有两次和三次等等……

I need some help with counting both unique and duplicate values in MySQL. I want to know how many records there are total, and also how many is there two times and three times and so on...

我需要使用 UNION 之类的东西吗?我认为 SUM 对我来说将是最好的解决方案，因为将来我可能会对此使用一些联接.

Do I need to use UNION or something? I think SUM would be the best solution for me because of I might use some joins with this in future.

样本数据:

| custId | name   |
|--------|--------|
| 1001   | Alex   |
| 1001   | Alex   |
| 1002   | Daniel |
| 1003   | Mark   |
| 1002   | Daniel |

样本结果:

| total | twoTimes | threeTimes |
|-------|----------|------------|
|     3 |        2 |          0 |

谢谢.

推荐答案

这是我的方法:

http://sqlfiddle.com/#!9/9411dc/3

SELECT c.cnt AS `times`, COUNT(c.name) cnt
FROM (SELECT name, COUNT(custId) cnt
FROM cust
GROUP BY name) c
GROUP BY c.cnt;

这与您要求的不完全相同(您要求的数据透视表很难实现).因此，如果要使其成为枢轴，可以在这里阅读: MySQL枢轴表

that is not exactly what you did ask (you asked for pivot table which is very difficult to realize). So if you want to make it pivot you can read here: MySQL pivot table

如果您确定最大重复次数很小，则数据透视查询可能是:

And if you are sure that you have very small max of duplicate count your pivot query could be:

http://sqlfiddle.com/#!9/9411dc/5

SELECT 
  SUM(IF(c.cnt=1,1,0)) AS `Unique`, 
  SUM(IF(c.cnt=2,1,0)) AS `Two times`, 
  SUM(IF(c.cnt=3,1,0)) AS `Three times`, 
  SUM(IF(c.cnt=4,1,0)) AS `Four times`
FROM (SELECT name, COUNT(custId) cnt
FROM cust
GROUP BY name) c

这篇关于MySQL计算唯一值和重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！