mysql - 如何在mysql中显示ID

Question

所以我有一个包含 ID 和名称的表。我有一个 where 条件

name = 'John'
name = 'Ariel'
name = 'Ariel'
name = 'Mary'
name = 'Mary'

我想显示它的 ID。所以输出应该是这样的:

id  name
1   John 
1   John
3   Ariel
3   Ariel
5   Mary
5   Mary

我的做法是这样的:

select id, name from users where
name = 'John' OR
name = 'John' OR
name = 'Ariel' OR
name = 'Ariel' OR
name = 'Mary' OR
name = 'Mary';

也尝试过

where name in ('John','John','Ariel','Ariel','Mary','Mary');

但它总是会这样输出:

id  name
1   John
3   Ariel
5   Mary

那么，即使条件重复，我如何显示 id 呢？我真的很想展示这样的东西:

id  name
1   John 
1   John
3   Ariel
3   Ariel
5   Mary
5   Mary

或者这甚至可能吗？

注意:我没有重复的记录。

Answer 1

您可以尝试在 suqyery 上使用基于 union all 的内部连接

select m.id, m.name 
from my_table  m 
inner join (
select 'John' as name  from dual 
union all 
select 'John' from dual 
union all 
select 'Ariel' from dual 
union all 
select 'Ariel' from dual 
union all 
select 'Mary' from dual 
union all 
select 'Mary' from dual 
) t on t.name =m.name