根据一列或两列选择所有重复的行？ [英] Select all duplicate rows based on one or two columns?

问题描述

我有一个表名为的联系人与字段

+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+

我想根据 first_name 和（/或） last_name ，例如：

I want to display all duplicates based on first_name and (/ or) last_name, e.g:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | mukta      | chourishi |
|  2 | mukta      | chourishi |
|  3 | mukta      | john      |
|  4 | carl       | thomas    |
+----+------------+-----------+

如果只搜索 first_name ，它应该返回：

If searched on just first_name it should return:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

但是，如果在 first_name 和 last_name 应该返回：

But if searched on both first_name and last_name should return:

+----+
| id |
+----+
|  1 |
|  2 |
+----+

推荐答案

实现结果的方式是使用嵌套查询和有效子句：在内部查询中选择那些数量超过一个，并且在外部查询中选择id：

One way to achieve your result is using nested query and having clause: In inner query select those having count more then one, and in outer query select id:

检查以下示例单列选择条件：

Check following example for single column selection criteria:

创建表：

CREATE TABLE `person` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `first` varchar(120) NOT NULL,
    `last` varchar(120) NOT NULL
);

插入元组：

INSERT INTO `person` ( `first`, `last`) VALUES
("mukta", "chourishi"),
("mukta", "chourishi"),
("mukta", "john"),
("carl", "thomas" );

您需要的结果：

mysql> SELECT  `id` 
    -> FROM `person` 
    -> WHERE `first`=(SELECT `first` FROM `person` HAVING COUNT(`first`) > 1);
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

[ANSWER]

但是，如果您的选择标准是基于多个列，那么您可以使用JOIN。

But as if you selection criteria is on the basis of more than one columns then you can make use of JOIN.

要解释它，我正在编写一个选择查询，创建一个将作为第二个操作数表在JOIN中使用的中间表。

To explain it I am writing a selection query that creates an intermediate table that will be use in JOIN as second operand table.

查询是选择所有的拳头名称和列，这些重复与其他一些行：

例如，选择第一个和 last 名称重复

Query is select all fist name and column those duplicates with some of other rows:
For example select rows in which first and last name repeats

mysql> SELECT `first`, `last`,  count(*)  as rows 
    -> FROM `person` 
    -> GROUP BY `first`, `last` 
    -> HAVING count(rows) > 1;
+-------+-----------+------+
| first | last      | rows |
+-------+-----------+------+
| mukta | chourishi |    2 |
+-------+-----------+------+
1 row in set (0.00 sec)

所以你只有一对第一个和最后一个命名那些重复（或与其他行重复）。

So you have only one pair of first and last names those repeats (or is duplicates with some other rows).

现在，问题是：如何选择 id 这行？使用加入！如下：

Now, question is: how to select id of this row? Use Join! as follows:

mysql> SELECT  p1.`id`
    -> FROM `person` as p1
    -> INNER JOIN (
    ->     SELECT `first`, `last`,  count(*)  as rows
    ->     FROM `person` 
    ->     GROUP BY `first`, `last` 
    ->     HAVING count(rows) > 1) as p
    -> WHERE p.`first` = p1.`first` and p.`last` = p1.`last`;  
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.06 sec)

您可以根据需要选择尽可能多的列，例如单列如果要使用join，则删除姓氏。

you can select on the basis of as many columns as you wants e.g. single column if you want using join then remove last name.

这篇关于根据一列或两列选择所有重复的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！