我如何得到一个字段值只出现一次的所有行?

Hope you can understand what I want to archive...

希望你能理解我想要存档的…

I have something like this

我有这样的东西。

table: my_user

表:my_user

id - username - user_profile - user_status - user_key

so, in the case i have a content like this

在这种情况下，我有这样的内容。

1 - John Doe - profile1 - ok - key12345
2 - John Doe - profile2 - ok - key12345
3 - John Doe - profile2 - ok - key12345
4 - John Doe - profile2 - ok - key12346
5 - John Doe - profile2 - ok - key12347

I do have all the different keys like

我有各种各样的钥匙。

key12345
key12346
key12347

Is there any way in a query where I can query my table asking for all the results that matches have this key BUT retrieving just the first record that has it? like:

在查询中有什么方法可以查询我的表，请求所有匹配的结果都有这个键，但只检索有它的第一个记录?如:

1 - John Doe - profile1 - ok - key12345
4 - John Doe - profile2 - ok - key12346
5 - John Doe - profile2 - ok - key12347

I tried with something like this

我试过这样的方法。

select * from my_user where user_key IN (key12345, key12346, key12347)

But I get all the rows, is it possible to do this?

但是我得到了所有的行，可以这样做吗?

2 个解决方案

#1

Can you try the following query:

你能试试下面的查询:

SELECT * 
FROM my_user u 
WHERE ID IN (
 SELECT MIN(ID) 
 FROM my_user 
 WHERE user_key = u.user_key  
)

Here's the SQL Fiddle.

这是SQL小提琴。

#2

Use variable to emulate row_number()

使用变量来模拟row_number()

SQL Demo

SQL演示

SELECT *
FROM (
       SELECT my_user.*,
              @rn := IF(@user = user_key,
                        @rn + 1,
                        IF(@user:= user_key, 1, 1)
                       ) as rn         
       FROM my_user 
       CROSS JOIN (SELECT @rn := 0, @user := 0) t
       where user_key IN ('key12345', 'key12346', 'key12347')
       ORDER BY user_key
     ) F
WHERE F.rn = 1
ORDER BY id;

OUTPUT

输出

#1