使用内部加入删除倒数/类似记录

发布于 2025-01-30 03:06:04 字数 501 浏览 4 评论 0原文

我有一个带有名字的桌子，其中一些是对其他人的速记，有些是相似的，但不相似。例如，Michael 和Mike是相互的，但Michael不是。我运行了一个脚本以获得一匹或双向匹配的脚本，例如

Michael | Mike
Mike | Michael

，这只是

Michael | Uncle Michael

表明它们不匹配对。

我正在尝试使用它来删除较短的匹配项（例如Mike）。

我有一个 sqlfiddle，证明了这一点，我只能找到匹配的对，但是，不确定现在如何进行删除T1以从所有匹配对中删除发现的记录的较短。

原文

I have a table with names, some of which are shorthand for others and some which are similar but are not. For instance Michael and Mike are reciprocal, yet Uncle Michael is not. I ran a script to get the either one- or two-way matching e.g.

Michael | Mike
Mike | Michael

yet only

Michael | Uncle Michael

which indicates they are not matching pairs.

I'm trying to use that to then remove the shorter matching term (e.g. Mike).

I have a SqlFiddle demonstrating this, I can get as far as finding only the matching pairs but am unsure how to now do a Delete t1 to delete the shorter of the found record from all of the matching pairs.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

怀中猫帐中妖 2025-02-06 03:06:04

这可能会从数据库服务器的角度为您提供一些见解。我们可以通过子句使用组来为名称中定义的组名称。例如“迈克”和“迈克尔”。然后，我们计算结果集中不同名称的数量。如果存在超过1个不同的名称，我们删除了较短的名称。否则，否则没有任何删除，因为我们可能要保留的只有1个不同的名称。

delete from Names where exists 
(
    select count(*) from 
    (select name from Names where (name='Michael' or name='Mike') group by name ) t 
    having count(*) >1
)
and name='Mike'
;

This might give you some insight from db server's perspective. We can use a group by clause to group names defined in a name-pair. e.g 'Mike' and 'Michael'. Then we count the number of distinct names in the result set . In the case when more than 1 distinct name exists, we delete the shorter one. Otherwise delete nothing as there is only 1 distinct name existing which we probably want to keep.

delete from Names where exists 
(
    select count(*) from 
    (select name from Names where (name='Michael' or name='Mike') group by name ) t 
    having count(*) >1
)
and name='Mike'
;

回复收藏 0 原文

~没有更多了~