例如,我有以下关系:
Suppliers( sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog( sid: integer, pid: integer, cost: real)
我写了下面的MYSQL查询来查找只提供红色部件的供应商的地址。
1)例子
SELECT DISTINCT C.sid FROM Catalog C
WHERE C.sid NOT IN(
SELECT C.sid FROM Catalog C
INNER JOIN Parts P ON p.pid = C.pid
WHERE P.color <> "red"
);
示例2)
SELECT DISTINCT C.sid FROM Catalog C
WHERE C.sid NOT IN(
SELECT C2.sid FROM Catalog C2
INNER JOIN Parts P ON p.pid = C2.pid
WHERE P.color <> "red"
);
我想知道是否有什么不同。以上两个查询语句。如果我想在嵌套查询中重用同一个表两次,我是否必须以不同的方式命名表别名?我假设内部查询在外部查询之前先执行,所以示例1应该是正确的,但我不能确定,因为示例2是教授教授的方式。
您的两个查询之间没有区别。它们完全一样。唯一的区别是您为表Catalog
使用了不同的别名。解析器将从内部到外部解析别名,因此在您的查询示例中1
SELECT DISTINCT C.sid FROM Catalog C
WHERE C.sid NOT IN(
SELECT C.sid FROM Catalog C
INNER JOIN Parts P ON p.pid = C.pid
WHERE P.color <> "red"
);
别名为C
的子查询的Catalog
将与外部查询不同。因为解析器会把它当作另一个表来解析。
无论您使用什么别名,解析器都将从内部(子查询)解析到外部。希望你能理解。
根据您使用的操作符,您可以在子查询中使用外部别名。就像下面的查询。
要解决这个问题,可以使用NOT EXISTS子句连接外部表,如下所示:
SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS (
SELECT 1
FROM Catalog Cin
INNER JOIN Parts P ON p.pid = Cin.pid
WHERE P.color <> 'red'
AND Cin.sid=C.sid );
您的查询将同时工作。如果您想在不使用子查询的情况下执行此操作,请尝试:
select c.sid
from Catalog c inner join Parts p on p.pid = c.sid
group by c.sid
having min(p.color) = 'red' and max(p.color) = 'red'
或
having count(case when p.color = 'red' then 1 else null end) = count(*)
如果你感兴趣的话,这里有另一种方法。它可以让你一次轻松地获取所有供应商信息:
select *
from Suppliers s
where 'red' = all (
select p.color
from Catalog c inner join Parts p on p.pid = c.pid
where c.sid = s.sid
)
实际上您不需要在这个场景中使用not in。你离答案很近了。请尝试以下查询;
select c.sid from Catalog as c
inner join Parts as p on c.pid=p.pid
WHERE P.color <> "red"
group by c.sid