mysql - 如何在 SQL 中查找以相同字符串结尾的行？

Question

我有一个类似于此处找到的问题:How to find rows in SQL that start with the same string (similar rows)? ，并且此解决方案适用于 MySQL 5.6 但不适用于 5.7。

我有一个包含多个列的数据库 (t)，重要的是 id 和文件路径，我想要完成的是检索所有最后 5 个字符相同的文件路径。以下在 MySQL5.6 中工作，第二个 SELECT 在 5.7 中工作正常:

SELECT id, filepath FROM t
WHERE SUBSTRING(filepath, -5) IN
(
  SELECT SUBSTRING(filepath, -5) 
  FROM t 
  GROUP BY SUBSTRING(filepath, -5)
  HAVING COUNT(*) > 1
)

但是当我尝试在 5.7 上运行它时出现错误

Expression #1 of HAVING clause is not in GROUP BY clause and contains 
nonaggregated column 't.filepath' which is not functionally dependent on 
columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by

示例数据:

id     filepath
1      /Desktop/file1.txt
2      /Desktop/file2.txt
3      /Desktop/file1.txt

我想返回 id 为 1 和 3 的行。我该如何为 MySQL5.7 解决这个问题？

编辑:还有谁能指出正确的方向让 SQL 删除重复项吗？所以我想删除 id 3 的条目，但保留 id 1 和 2 的条目。

Answer 1

请阅读有关 GROUP BY 和 sql_mode only_full_group_by 主题的 mysql 文档(如您的错误消息所述): https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

我认为将内部查询更改为此可能会解决问题:

SELECT SUBSTRING(filepath, -5) AS fpath
FROM t 
GROUP BY fpath
HAVING COUNT(fpath) > 1

编辑:

关于为什么添加“AS fpath”有效的问题:添加别名“fpath”只是一种干净的方法。 ONLY_FULL_GROUP_BY 的要点是您在 SELECT、HAVING 或 ORDER BY 中使用的每个字段也必须在 GROUP BY 中。

所以我出于多种原因添加了 fpath-alias:

• 为了性能:您编写的查询有两次 SUBSTRING(filepath, -5)，这对性能不利。 Mysql 必须执行该 SUBSTRING 调用两次，而在我的例子中，它只需执行一次(每行)。
• 解决分组问题:您有 COUNT() 个，但“”不在您的 GROUP BY 语句中(我什至不确定这是否可能)。您必须计算“某物”，因此由于“fpath”在您的 SELECT 和 GROUP BY 中，将其用作 COUNT() 可以解决问题。