首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何在SQL中找到以相同字符串结尾的行?

问如何在SQL中找到以相同字符串结尾的行?
EN

Stack Overflow用户
提问于 2017-09-19 18:27:46
回答 3查看 495关注 0票数 1

我有一个类似于这里发现的问题:How to find rows in SQL that start with the same string (similar rows)?,这个解决方案在MySQL 5.6中工作,而不是5.7。

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

代码语言:javascript
复制
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上运行它时,我得到了错误

代码语言:javascript
复制
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

样本数据:

代码语言:javascript
复制
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的条目。

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2017-09-19 18:44:37

请阅读关于主题组和sql_mode only_full_group_by的mysql文档(就像您的错误消息说的那样):https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

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

代码语言:javascript
复制
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中。

因此,由于多种原因,我添加了fpath-别名:

  • 性能:您编写的查询有两次SUBSTRING(filepath,-5),这不利于性能。Mysql必须执行两次SUBSTRING调用,而在我的示例中,它只需要执行一次(每行)。
  • 要解决组的问题:您在拥有中有COUNT(),但是"“不是通过语句在组中进行的(我甚至不确定这是否可能)。您必须对“某事”进行计数,因此由于"fpath“在您选择的组和组中,因此使用它可以解决问题。
票数 1
EN

Stack Overflow用户

发布于 2017-09-19 19:05:40

我不喜欢将子查询放在IN()谓词中,因为MySQL倾向于多次运行子查询。

您可以以不同的方式编写查询,将子查询作为派生表放在FROM子句中。这将使MySQL只运行一次子查询。

代码语言:javascript
复制
SELECT id, filepath 
FROM (
  SELECT SUBSTRING(filepath, -5) AS suffix, COUNT(*) AS count
  FROM t 
  GROUP BY suffix
  HAVING count > 1
) AS t1
JOIN t AS t2 ON SUBSTRING(t2.filepath, -5) = t1.suffix

但是,这肯定会执行表格扫描,所以这将是一个代价高昂的查询。在进行这样的子字符串比较时,它不能使用索引。

要优化这一点,您可以创建一个包含索引的虚拟列。

代码语言:javascript
复制
ALTER TABLE t 
  ADD COLUMN filepath_last VARCHAR(10) AS (SUBSTRING_INDEX(filepath, '/', -1)),
  ADD KEY (filepath_last);

然后您可以像这样查询它,并且至少子查询使用一个索引:

代码语言:javascript
复制
SELECT id, filepath 
FROM (
  SELECT filepath_last, COUNT(*) AS count
  FROM t 
  GROUP BY filepath_last
  HAVING count > 1
) AS t1
STRAIGHT_JOIN t AS t2 ON t2.filepath_last = t1.filepath_last
票数 1
EN

Stack Overflow用户

发布于 2017-09-19 19:07:47

在这里找到了为我工作的解决方案:BY

我运行了SELECT @@sql_mode,然后是SET @@sql_mode =,后面是包含第一个查询返回的所有值的字符串,除了only_full_group_by之外,但我仍然感兴趣的是如何在不更改only_full_group_by设置的情况下完成这一任务。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/46307430

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档