如何计算 SQL 中的唯一值对？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5911320/
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
How can I count unique pairs of values in SQL?
提问by Jay Askren
With the following sql statement I can get all unique values with their counts for a given column:
使用以下 sql 语句，我可以获得给定列的所有唯一值及其计数：
select column, count(column) as count
from table group by column order by count desc;
How would I get all unique pairs of values with counts. For instance if I had a table of with columns first_name and last_name, I might find results something like this:
我将如何获得所有具有计数的唯一值对。例如，如果我有一个包含 first_name 和 last_name 列的表，我可能会发现如下结果：
first_name | last_name | count
John | Smith | 42
John | Johnson | 39
David | Smith | 37
名字| 姓氏 | 数数
约翰 | 史密斯 | 42
约翰 | 约翰逊 | 39
大卫 | 史密斯 | 37
etc...
等等...
Can I do this in basic SQL? I generally use MySQL, but I assume whatever solution you come up with should be translatable to any db.
我可以在基本 SQL 中执行此操作吗？我通常使用 MySQL，但我认为您提出的任何解决方案都应该可以转换为任何数据库。
回答by Ryan
You've almost got it correct... You just add an additional GROUP BYcolumn like so:
您几乎已经猜对了……您只需添加一个额外的GROUP BY列，如下所示：
SELECT [first_name], [last_name], COUNT(*) AS [Count]
FROM [Table]
GROUP BY [first_name], [last_name]
ORDER BY [Count] DESC;
回答by adam
If you just want the count of how many distinct pairs, you could do this more simply.
A GROUP BYclause is not necessary.
如果您只想计算不同对的数量，则可以更简单地执行此操作。一个GROUP BY条款是没有必要的。
SELECT COUNT(DISTINCT first_name, last_name) AS count_names FROM Table
回答by tschaible
Use multiple columns in your group by clause.
在 group by 子句中使用多列。
select first_name, last_name, count(*) as count from table group by first_name, last_name
回答by obscure
SELECT first_name, last_name, COUNT(distinct last_name) AS c
FROM ttable
GROUP BY first_name, last_name
HAVING c > 999
ORDER BY c DESC
Adding distinct will do it in MYSQL. Thanks
添加 distinct 将在 MYSQL 中完成。谢谢
