选择前n个字符相等的行(MySQL) [英] Selecting rows where first n-chars are equal (MySQL)

问题描述

我有一个带有玩家手柄的桌子，像这样:

I have a table with playerhandles, like this:

1 - [N] Laka
2 - [N] James
3 - nor | Brian
4 - nor | John
5 - Player 2
6 - Spectator
7 - [N] Joe 

从那里我想选择所有前n个字符匹配的所有玩家，但我不知道模式，只知道它是前n个字符.在上面的示例中，我不希望返回行1,2、3、4和7.

From there I wanna select all players where the first n-chars match, but I don't know the pattern, only that it's the first n-chars. In the above example I wan't it to return rows 1,2,3,4 and 7.

在MySQL中这样做有可能而且不太昂贵吗?

Is this possible and not too expensive to do in MySQL?

推荐答案

您可以添加一个exist子句.

You could add an exists clause.

select name from players p1 where exists (
  select 1 from players p2 where 
    p2.name like CONCAT( SUBSTRING(p1.name, 1, 3), '%') 
    and p1.name <> p2.name )

这将为您提供:
1-[N]拉卡
2-[N]詹姆斯
3-也不|布莱恩
4-否|约翰
7-[N]乔

This will give you:
1 - [N] Laka
2 - [N] James
3 - nor | Brian
4 - nor | John
7 - [N] Joe

在名称上添加一个"order by"，然后您就可以用代码完成其余的工作.

Add an 'order by' on the name, and you can do the rest of your work in code.

这篇关于选择前n个字符相等的行(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！