比较同一个表上相同列的值

This has got to be a simple question, just brain-dumbing right now...

这必须是一个简单的问题,现在只是大脑笨拙......

I've got one table, called 'foo'. It's got two columns, 'id' and 'username'.

我有一张桌子,叫做'foo'。它有两列,'id'和'username'。

The id is unique, but some of the usernames reference the same user; just one has a prefix on the username of 'xx_'.

id是唯一的,但是一些用户名引用同一个用户;只有一个在'xx_'的用户名上有一个前缀。

ex:
ID      USERNAME
1       bob
2       sam
3       xx_bob

How can I figure out which of the users have a counterpart with the 'xx_' prefix? And then which ones do not?

如何确定哪些用户具有“xx_”前缀的对应物?然后哪些不?

3 个解决方案

#1

select * from foo where username 
 IN (select replace(username, 'xx_', '') from foo where username like 'xx_%')

What this does is compares the entire table against a sub list which is generated by the sub-query after the IN verb.

这样做是将整个表与IN动词后的子查询生成的子列表进行比较。

To do the opposite you can simply use a NOT IN in place of the IN.

要做相反的事情,您只需使用NOT IN代替IN。

NOTE: This is a t-sql (MS SQL 2005) query, should be similar in MySQL

注意:这是一个t-sql(MS SQL 2005)查询,在MySQL中应该类似

#2

This will give you the id's of both rows:

这将为您提供两行的ID:

select * from foo a1 join foo a2 on (a2.username=concat('xx_',a1.username));

#3

If you want every rows that isn't a duplicate, with the duplicate_id:

如果您希望每个行都不重复,请使用duplicate_id:

SELECT foo.*, f2.id AS duplicate_id FORM foo
LEFT OUTER JOIN foo AS f2 ON ( f2.username = concat( 'xx_', foo.username ) )
WHERE foo.id NOT LIKE 'xx_%'

#1

select * from foo where username 
 IN (select replace(username, 'xx_', '') from foo where username like 'xx_%')