子查询中基于函数依赖的MySQL组

Question

我正在编写一个查询，以在人员表(包括每个副本)中查找重复行：

SELECT *
FROM Person
WHERE CONCAT(firstName,lastName) IN (
    SELECT CONCAT(firstName,lastName) AS name
    FROM Person
    GROUP BY CONCAT(firstName,lastName)
    HAVING COUNT(*) > 1
)

在启用了MySQL 8.0.19的MySQL 8.0.19中运行此操作时，如果出现以下错误，将失败：

查询1错误: with子句的表达式#1不按子句分组，而是包含非聚合列'Person.firstName‘，该列在功能上不依赖于按子句分组的列；这与sql_mode=only_full_group_by不兼容

我想不出怎么解决这个问题。我试着将COUNT(*)改为COUNT(CONCAT(firstName,lastName))，但这并没有帮助。

奇怪的是，a)它在MariaDB 10.2中运行得很好，不管有没有ONLY_FULL_GROUP_BY，b)单独运行子查询本身就没有问题。

我做错了什么？这似乎是MySQL中的一个bug。

编辑:我当然很欣赏我的查询的替代解决方案，但是我真的很想知道为什么会发生错误。

Answer 1

试着像下面一样，它会和你试过的一样

SELECT *
FROM Person
WHERE (firstName,lastName) IN (
    SELECT firstName,lastName
    FROM Person
    GROUP BY firstName,lastName
    HAVING COUNT(*) > 1
)

Answer 2

不要合并字段：

SELECT *
FROM Person
WHERE (firstName,lastName) IN (
    SELECT firstName,lastName AS name
    FROM Person
    GROUP BY firstName,lastName
    HAVING COUNT(*) > 1
)

或者使用ANY_VALUE()函数：

SELECT *
FROM Person
WHERE CONCAT(firstName,lastName) IN (
    SELECT ANY_VALUE(CONCAT(firstName,lastName)) AS name
    FROM Person
    GROUP BY CONCAT(firstName,lastName)
    HAVING COUNT(*) > 1
)

Answer 3

我将使用现有逻辑编写您的查询：

SELECT p1.*
FROM Person p1
WHERE EXISTS (SELECT 1 FROM Person p2
              WHERE p2.firstName = p1.firstName AND
                    p2.lastName = p1.lastName AND
                    p2.id <> p1.id);

这实际上意味着我们可以为每个人选择另一个不同的人(通过主键id列，或者PK可能是什么)，具有相同的名字和姓氏。

以下索引可加快上述查询的速度：

CREATE INDEX idx ON Person (lastName, firstName);

这应该允许存在查找快速评估。注意，在InnoDB上，MySQL应该通过将它添加到上述两列索引的末尾来自动覆盖id。

关于您的错误，我不禁想知道，问题是否在于您没有在子查询中使用正确的别名，从而导致MySQL认为您在引用外部查询中的列。试试这个版本：

SELECT p1.*
FROM Person p1
WHERE CONCAT(firstName, lastName) IN (
    SELECT CONCAT(p2.firstName, p2.lastName)
    FROM Person p2
    GROUP BY CONCAT(p2.firstName, p2.lastName)
    HAVING COUNT(*) > 1
);