MySQL：计算行中唯一的值对 [英] MySQL: Count unique pairs of values in rows

问题描述

我遇到了一个问题，我不知道如何解决。这是我的表'myTable'看起来像：

I'm stuck with a problem I don't know how to solve. Here's what my table 'myTable' look like:

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| one      | varchar(10) | YES  |     | NULL    |                |
| two      | varchar(10) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

mysql> SELECT * FROM myTable; 

+----+-----------------+------------+
| id | one             | two        |
+----+-----------------+------------+
|  1 | cat             | steve      |
|  2 | cat             | steve      |
|  3 | cat             | adam       |
|  4 | dog             | john       |
|  5 | dog             | adam       |
|  6 | dog             | alice      |
|  7 | mouse           | peter      |
|  8 | mouse           | peter      |
|  9 | mouse           | peter      |
+----+-----------------+------------+

我后面的是一个查询，为'one'中的每个唯一值给出了'two'它给了我：
dog 3（三个唯一的名字）
cat 2（两个唯一的名字）
鼠标1（一个唯一的名字）

What I'm after is a query that for each unique value in 'one' gives me the count of unique values in 'two', i.e. that gives me: dog 3 (three unique names) cat 2 (two unique names) mouse 1 (one unique name)

我已经搜索过，但不知道如何找到正确的答案，并得到了：

I have searched around, but don't know how to find the right answer, and gotten as far as:

SELECT one, two, COUNT(two) AS nr FROM myTable GROUP BY one, two ORDER BY one;

任何建议非常感谢！提前感谢。

Any suggestions much appreciated! Thanks in advance.

推荐答案

由于您需要计算不同的名称，因此您需要使用 distinct 关键字。

As you need to count distinct name you need to use distinct keyword.

尝试此查询

select 
   one,  
   count(distinct two) as cnt 
from 
   tbl 
group by 
   one

SQL FIDDLE

SQL FIDDLE

|   ONE | CNT |
---------------
|   cat |   2 |
|   dog |   3 |
| mouse |   1 |

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