删除带有字符串的记录=由另一个字段分组的另一个记录的字符串的结尾 [英] Remove Records with string that = end of another Record's string grouped by another field

问题描述

尽管这可能很笨拙，但我做了一个查询，它会逐步遍历并识别整个名称字段构成另一个名称字段末尾的记录:

As clumsy as this may be, I made a Query that steps through and identifies records whose entire name field makes up the end of another name field:

查找包含的记录

我真正想做的是:

1. Find 只查找来自同一客户的其他记录中包含的那些记录，这是我得到的:

尝试按客户端字段对包含的记录进行分组

1. 删除这些记录(来自每个客户的记录已包含在该客户的另一条记录中.

我知道我可以用类似的东西来做到这一点

I get I can do this with something like

从 Main where Name='Farm' 和 Client='Bob' 中删除

但不确定如何将我的选择变成所需的分组.

But not sure how to turn my select yet into the grouping required.

推荐答案

可以使用连接条件T.Name Like Concat('%_', T2.Name).这意味着 T.Name 以 T2.Name 结尾，但前面至少还有一个字符 (_).

You can use the join condition T.Name Like Concat('%_', T2.Name). That means T.Name ends with T2.Name, but has at least one more character (_) in front of it.

Select T.Client,T.Client,T.Name,T2.Name
From Main T
Inner Join Main T2
  On  T2.Client = T.Client
  And T.Name Like Concat('%_', T2.Name)
Where T2.Name != ''

演示:http://sqlfiddle.com/#!9/bf016e/26

将其转换为 DELETE 语句:

Convert it to a DELETE statement:

Delete T2
From Main T
Inner Join Main T2
  On  T2.Client = T.Client
  And T.Name Like Concat('%_', T2.Name)
Where T2.Name != ''

演示:http://sqlfiddle.com/#!9/9503cd/1

这也应该有效，而且可能不那么神秘:

This should also work and might be less cryptic:

And T.Name <> T2.Name
And T.Name Like Concat('%', T2.Name)

这篇关于删除带有字符串的记录=由另一个字段分组的另一个记录的字符串的结尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！