将子查询替换为同一表的连接

Question

在这种情况下，预期的结果可以在没有子查询的情况下完成吗？也许使用联接？

我们有一个名字说"jose"，预期结果是所有与 jose 颜色相同的行。查询应该在 MS-SQL 和 ORACLE 中运行。

query
======
select name,color from tableA where color=(select color from tableA where name='jose')
Expected result 
===============
name    color
jose    red
Rap     red
schema
=======
Table and DATA
create table tableA (
name varchar(10),
color varchar(10)
);
insert into tableA values ('jose','red');
insert into tableA values ('Mickey','blue');
insert into tableA values ('Leo','yellow');
insert into tableA values ('Rap','red');
insert into tableA values ('Don','blue');

http://sqlfiddle.com/#!18/5f7e3/2

Answer 1

您可以通过在color字段上自连接来获得JOIN结果，其中第二个表中的名称jose：

SELECT a1.name, a1.color
FROM tableA a1
JOIN tableA a2 ON a2.color = a1.color AND a2.name = 'jose'

输出

name    color
jose    red
Rap     red

SQL Server 演示 on SQLFIddle

Oracle demo on SQLFiddle

Answer 2

如果名称只有一种颜色，那么您似乎需要：

select a.*
from tableA a
where a.color = (select a2.color from tableA a2 where a2.name = 'jose');

您可能希望添加另一个条件，如果您不想返回该行，则a.name <> 'jose'该条件。

Answer 3

我认为最好的方法是"CTE"：

with 
cte1 as (
select 1 as RowToJoin,
name,
color 
from tableA
),
cte2 as (
select 1 as RowToJoin,
color
name
from tableA 
where name='jose'
)
select c1.name, c1.color
from cte1 c1
join cte2 c2 on c2.RowToJoin = c1.RowToJoin
where c1.name <> c2.name

它看起来很难，但它很简单。尝试阅读它。！