sql - 显示与两个 ID 关联的记录

Question

我有一张 table

ID NAME
--------
1  AAA
2  BBB
2  AAA
2  CCC
1  DDD
2  DDD

我必须显示与 ID 1 和 2 链接的记录

NAME
----
AAA
DDD

我正在使用以下查询 -

Select Name from table1 where ID IN (1,2);

但它正在向我展示 -

NAME
-----
AAA
BBB
CCC
DDD

如何更改查询以解决此问题？

Answer 1

SELECT DISTINCT NAME
FROM tabel1 t1
join table1 t2
on t1.id = 1 and t2.id = 2 and t1.name = t2.name

或者如果可以有很多匹配

SELECT DISTINCT NAME
FROM tabel1 t1
WHERE EXISTS (SELECT 1 FROM table1 t2 WHERE t1.name = t2.name and t2.id = 2)
and t1.id = 1

或者

SELECT NAME FROM tabel1 WHERE id = 1
INTERSECT
SELECT NAME FROM tabel1 WHERE id = 2