Mysql匹配两列并获得计数

Question

我怎么算？

+---------+-------------+
|   col1  |   col2      |  
+---------+-------------+
|     Joe | April       |
|    Fred | May         |
|     Sam | April       | 
|     Joe | April       |
|    Mark | Jan         | 
|     Joe | Dec         |             
+---------+-------------+

在MYSQL中，如果月份也匹配，如何计算匹配的名称？乔·艾普丽尔应该只有一个..。

Answer 1

@Abdul的答复只是需要HAVING COUNT(*) > 1

查询现在应该如下所示

SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;

以下是它的执行情况：

mysql> create table test(col1 varchar(20),col2 varchar(20));
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO test VALUES('Joe','April'),('Fred','May'),
    -> ('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+----------+------+-------+
| COUNT(*) | col1 | col2  |
+----------+------+-------+
|        2 | Joe  | April |
+----------+------+-------+
1 row in set (0.24 sec)

mysql>

如果您不关心计数，请从SELECT子句中删除它。

mysql> SELECT col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+------+-------+
| col1 | col2  |
+------+-------+
| Joe  | April |
+------+-------+
1 row in set (0.00 sec)

mysql>

试试看！

Answer 2

创建一个表并插入记录

create table test(col1 varchar(20),col2 varchar(20));

INSERT INTO test VALUES('Joe','April'),('Fred','May'),('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');

SELECT * FROM test;
+------+-------+
| col1 | col2  |
+------+-------+
| Joe  | April |
| Fred | May   |
| Sam  | April |
| Joe  | April |
| Mark | Jan   |
| Joe  | Dec   |
+------+-------+
6 rows in set (0.00 sec)

查找实际结果

SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2;
+----------+------+-------+
| COUNT(*) | col1 | col2  |
+----------+------+-------+
|        1 | Fred | May   |
|        2 | Joe  | April |
|        1 | Joe  | Dec   |
|        1 | Mark | Jan   |
|        1 | Sam  | April |
+----------+------+-------+
5 rows in set (0.00 sec)