返回在 MariaDB 中具有主键的重复行的 7 种方法

以下是当 MariaDB 中具有主键或其他唯一标识符列时返回重复行的七种方法。

因此，重复行在所有列中共享完全相同的值，但其唯一标识符列除外。

示例数据

我们将使用以下数据作为示例：

SELECT * FROM Dogs;

结果：

+——-+———–+———-+ | DogId | FirstName | LastName | +——-+———–+———-+ | 1 | Bark | Smith | | 2 | Bark | Smith | | 3 | Woof | Jones | | 4 | Ruff | Robinson | | 5 | Wag | Johnson | | 6 | Wag | Johnson | | 7 | Wag | Johnson | +——-+———–+———-+

前两行是重复的（列除外，列是表的主键，并且包含所有行的唯一值）。最后三行也是重复的（列除外）。DogId``DogId

主键列确保没有重复的行，这在RDBMSs中通常是一件好事。但是，根据定义，这意味着没有重复项。在我们的例子中，主键列是一个递增的数字，它的值没有任何意义，也不重要。因此，如果我们想_在重要的列_中查找重复项，我们需要忽略该行。

选项 1

我们可以使用 theclause 按列的有效列对列进行分组，然后使用 thefunction 返回相同行数：GROUP BYCOUNT()

SELECT 
    FirstName, 
    LastName, 
    COUNT(*) AS Count
FROM Dogs
GROUP BY FirstName, LastName;

结果：

+———–+———-+——-+ | FirstName | LastName | Count | +———–+———-+——-+ | Bark | Smith | 2 | | Ruff | Robinson | 1 | | Wag | Johnson | 3 | | Woof | Jones | 1 | +———–+———-+——-+

我们能够通过从查询中省略主键列来排除它。

结果告诉我们，有两行包含Bark Smith，三行包含Wag Johnson。这些是重复的（在Wag Johnson的情况下是一式三份）。其他两行没有任何重复项。

选项 2

我们可以使用子句从输出中排除非重复项：HAVING

SELECT 
    FirstName, 
    LastName, 
    COUNT(*) AS Count
FROM Dogs
GROUP BY FirstName, LastName
HAVING COUNT(*) > 1;

结果：

+———–+———-+——-+ | FirstName | LastName | Count | +———–+———-+——-+ | Bark | Smith | 2 | | Wag | Johnson | 3 | +———–+———-+——-+

选项 3

还可以检查串联列上的重复项。例如，我们可以使用 thefunction 连接我们的两列，使用 thekeyword 获取不同的值，然后使用 thefunction 返回计数：CONCAT()DISTINCT``COUNT()

SELECT
    DISTINCT CONCAT(FirstName, ' ', LastName) AS DogName,
    COUNT(*) AS Count
FROM Dogs
GROUP BY CONCAT(FirstName, ' ', LastName);

结果：

+—————+——-+ | DogName | Count | +—————+——-+ | Bark Smith | 2 | | Ruff Robinson | 1 | | Wag Johnson | 3 | | Woof Jones | 1 | +—————+——-+

选项 4

我们可以将函数与子句一起使用：ROW_NUMBER()``PARTITION BY

SELECT 
    *,
    ROW_NUMBER() OVER ( 
        PARTITION BY FirstName, LastName 
        ORDER BY FirstName, LastName
        ) AS Row_Number
FROM Dogs;

结果：

+——-+———–+———-+————+ | DogId | FirstName | LastName | Row_Number | +——-+———–+———-+————+ | 1 | Bark | Smith | 1 | | 2 | Bark | Smith | 2 | | 4 | Ruff | Robinson | 1 | | 6 | Wag | Johnson | 1 | | 5 | Wag | Johnson | 2 | | 7 | Wag | Johnson | 3 | | 3 | Woof | Jones | 1 | +——-+———–+———-+————+

这将创建一个新列，其行号在每次出现重复项时递增，但在存在唯一行时再次重置。

在本例中，我们不会对结果进行分组，这意味着我们可以看到每个重复的行，包括其唯一标识符列。

选项 5

我们还可以将前面的示例用作较大查询中的公共表表达式：

WITH cte AS 
    (
        SELECT 
            *,
            ROW_NUMBER() OVER ( 
                PARTITION BY FirstName, LastName 
                ORDER BY FirstName, LastName
                ) AS Row_Number
        FROM Dogs
    )
SELECT * FROM cte WHERE Row_Number <> 1;

结果：

+——-+———–+———-+————+ | DogId | FirstName | LastName | Row_Number | +——-+———–+———-+————+ | 2 | Bark | Smith | 2 | | 5 | Wag | Johnson | 2 | | 7 | Wag | Johnson | 3 | +——-+———–+———-+————+

这将从输出中排除非重复项，并从输出中排除每个重复项的一行。

此查询可用作重复数据消除操作的前兆。它可以向我们显示如果我们决定删除重复项，将删除的内容。要对表进行重复数据消除，我们需要做的就是替换最后一个。SELECT *``DELETE

选项 6

下面是获取与上一个示例相同的输出的更简洁的方法：

SELECT * FROM Dogs 
WHERE DogId IN (
    SELECT DogId FROM Dogs 
    EXCEPT SELECT MIN(DogId) FROM Dogs 
    GROUP BY FirstName, LastName
    );

结果：

+——-+———–+———-+ | DogId | FirstName | LastName | +——-+———–+———-+ | 2 | Bark | Smith | | 6 | Wag | Johnson | | 7 | Wag | Johnson | +——-+———–+———-+

此示例不需要生成我们自己的单独行号。

我们可以替换以删除重复项。SELECT *``DELETE

选项 7

最后，这是返回重复项的另一个选项：

SELECT * 
FROM Dogs d1, Dogs d2 
WHERE d1.FirstName = d2.FirstName 
AND d1.LastName = d2.LastName
AND d1.DogId <> d2.DogId 
AND d1.DogId = (
    SELECT MAX(DogId) 
    FROM Dogs d3 
    WHERE d3.FirstName = d1.FirstName 
    AND d3.LastName = d1.LastName
);

结果：

+——-+———–+———-+——-+———–+———-+ | DogId | FirstName | LastName | DogId | FirstName | LastName | +——-+———–+———-+——-+———–+———-+ | 2 | Bark | Smith | 1 | Bark | Smith | | 7 | Wag | Johnson | 5 | Wag | Johnson | | 7 | Wag | Johnson | 6 | Wag | Johnson | +——-+———–+———-+——-+———–+———-+