如何计算 SQL 中的唯一值对? [英] How can I count unique pairs of values in SQL?
问题描述
使用以下 sql 语句,我可以获得给定列的所有唯一值及其计数:
With the following sql statement I can get all unique values with their counts for a given column:
select column, count(column) as count
from table group by column order by count desc;
我将如何获得所有具有计数的唯一值对.例如,如果我有一个包含 first_name 和 last_name 列的表,我可能会找到类似这样的结果:
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:
名字|姓氏 |计数
约翰 |史密斯 |42
John | Smith | 42
约翰 |约翰逊 |39
John | Johnson | 39
大卫 |史密斯 |37
David | Smith | 37
等等...
我可以在基本 SQL 中执行此操作吗?我通常使用 MySQL,但我认为您提出的任何解决方案都应该可以转换为任何数据库.
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.
推荐答案
您几乎答对了...您只需添加一个额外的 GROUP BY
列,如下所示:
You've almost got it correct... You just add an additional GROUP BY
column like so:
SELECT [first_name], [last_name], COUNT(*) AS [Count]
FROM [Table]
GROUP BY [first_name], [last_name]
ORDER BY [Count] DESC;
这篇关于如何计算 SQL 中的唯一值对?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!