在MariaDB中选择重复行的4种方法

如果您怀疑某个表在 MariaDB 数据库中有重复的行，则可以使用以下任何查询来了解有多少行是重复的。

示例数据

假设我们有一个包含以下数据的表：

SELECT * FROM Pets;

结果：

+———+———–+———–+ | PetId | PetName | PetType | |———+———–+———–| | 1 | Wag | Dog | | 1 | Wag | Dog | | 2 | Scratch | Cat | | 3 | Tweet | Bird | | 4 | Bark | Dog | | 4 | Bark | Dog | | 4 | Bark | Dog | +———+———–+———–+

前两行是重复的，后三行是重复的。

选项 1

我们可以使用以下查询来查看有多少行是重复的：

SELECT 
    DISTINCT PetId, 
    COUNT(*) AS "Count"
FROM Pets
GROUP BY PetId
ORDER BY PetId;

结果：

+———+———+ | PetId | Count | |———+———| | 1 | 2 | | 2 | 1 | | 3 | 1 | | 4 | 3 | +———+———+

如果需要，我们可以扩展列表以包含更多列：SELECT

SELECT 
    PetId,
    PetName,
    PetType,
    COUNT(*) AS "Count"
FROM Pets
GROUP BY 
    PetId,
    PetName,
    PetType
ORDER BY PetId;

结果：

+———+———–+———–+———+ | PetId | PetName | PetType | Count | |———+———–+———–+———| | 1 | Wag | Dog | 2 | | 2 | Scratch | Cat | 1 | | 3 | Tweet | Bird | 1 | | 4 | Bark | Dog | 3 | +———+———–+———–+———+

我们也可以按计数降序排序，以便重复项最多的行首先出现：

SELECT 
    PetId,
    PetName,
    PetType,
    COUNT(*) AS "Count"
FROM Pets
GROUP BY 
    PetId,
    PetName,
    PetType
ORDER BY Count DESC;

结果：

+——-+———+———+——-+ | PetId | PetName | PetType | Count | +——-+———+———+——-+ | 4 | Bark | Dog | 3 | | 1 | Wag | Dog | 2 | | 3 | Tweet | Bird | 1 | | 2 | Scratch | Cat | 1 | +——-+———+———+——-+

选项 2

如果我们只想列出重复的行，我们可以使用 theclause 来排除非重复的行：HAVING

SELECT 
    PetId,
    PetName,
    PetType,
    COUNT(*) AS "Count"
FROM Pets
GROUP BY 
    PetId,
    PetName,
    PetType
HAVING COUNT(*) > 1
ORDER BY PetId;

结果：

+——-+———+———+——-+ | PetId | PetName | PetType | Count | +——-+———+———+——-+ | 1 | Wag | Dog | 2 | | 4 | Bark | Dog | 3 | +——-+———+———+——-+

选项 3

另一种方法是将函数与子句一起使用，对结果集的输出进行编号。ROW_NUMBER()``PARTITION BY

SELECT 
    *, 
    ROW_NUMBER() OVER ( 
        PARTITION BY PetId, PetName, PetType 
        ORDER BY PetId, PetName, PetType
        ) AS Row_Number
FROM Pets;

Result:

+——-+———+———+————+ | PetId | PetName | PetType | Row_Number | +——-+———+———+————+ | 1 | Wag | Dog | 1 | | 1 | Wag | Dog | 2 | | 2 | Scratch | Cat | 1 | | 3 | Tweet | Bird | 1 | | 4 | Bark | Dog | 1 | | 4 | Bark | Dog | 2 | | 4 | Bark | Dog | 3 | +——-+———+———+————+

该子句将子句生成的结果集划分为应用该函数的分区。当我们为结果集指定分区时，每个分区都会导致重新开始编号（即每个分区中第一行的编号将从 1 开始）。PARTITION BY``FROM

选项 4

要从匹配的重复项中仅返回多余的行，我们可以将上面的查询用作公共表表达式，如下所示：

WITH cte AS 
    (
        SELECT 
            *, 
            ROW_NUMBER() OVER ( 
                PARTITION BY PetId, PetName, PetType 
                ORDER BY PetId, PetName, PetType
                ) AS Row_Number
        FROM Pets
    )
SELECT * FROM cte WHERE Row_Number <> 1;

结果：

+——-+———+———+————+ | PetId | PetName | PetType | Row_Number | +——-+———+———+————+ | 1 | Wag | Dog | 2 | | 4 | Bark | Dog | 2 | | 4 | Bark | Dog | 3 | +——-+———+———+————+