NOT IN with Subquery SQL构造
NOT IN with Subquery SQL构造
提问于 2013-05-03 00:03:56
ACTOR (id, fname, lname, gender)
MOVIE (id, name, year, rank)
CASTS (pid, mid, role)
WHERE pid references ACTOR id
mid references Movie id列出没有y的x去过的电影(x和y是演员)。
我发现很难用NOT in来构造SQL。这是我的尝试。我无法完成它，因为第二个演员不在
SELECT m.name
FROM MOVIE m
WHERE m.id NOT IN (SELECT c.mid
FROM CASTS c, ACTOR a
WHERE c.pid = a.id AND a.name = "adam..") 回答 4
Stack Overflow用户
回答已采纳
发布于 2013-05-03 00:38:21
使用NOT EXISTS
SELECT m.name -- Show the names
FROM movie m -- of all movies
WHERE EXISTS -- that there was
( SELECT * -- a role
FROM casts c -- casted to
JOIN actor a -- actor with
ON c.pid = a.id
WHERE c.mid = m.id
AND a.name = 'Actor X' -- name X
)
AND NOT EXISTS -- and there was not
( SELECT * -- any role
FROM casts c -- casted
JOIN actor a -- to actor with
ON c.pid = a.id
WHERE c.mid = m.id
AND a.name = 'Actor Y' -- name Y
) ;您也可以使用NOT IN.请注意，如果movie.id或casts.mid列中有NULL的行，这可能会给您带来意外的结果：
SELECT m.name -- Show the names
FROM movie m -- of all movies
WHERE m.id IN -- but keep only the movies that
( SELECT c.mid -- movies that
FROM casts c -- had a role casted to
JOIN actor a -- actor with
ON c.pid = a.id
WHERE a.name = 'Actor X' -- name X
)
AND m.id NOT IN -- and not the movies
( SELECT c.mid -- that
FROM casts c -- had a role casted
JOIN actor a -- to actor with
ON c.pid = a.id
WHERE a.name = 'Actor Y' -- name Y
) ;Stack Overflow用户
发布于 2013-05-03 00:24:56
您还可以使用经常被忽略的MINUS
SELECT Movie.id, Movie.name
FROM Actor
INNER JOIN Casts ON Actor.id = Casts.pid
INNER JOIN Movie ON Casts.mid = Movie.id
WHERE Actor.id = 1
MINUS SELECT Movie.id, Movie.name
FROM Actor
INNER JOIN Casts ON Actor.id = Casts.pid
INNER JOIN Movie ON Casts.mid = Movie.id
WHERE Actor.id = 2上面查询中的WHERE Actor.id可以替换为一些其他方式来唯一地标识参与者，例如通过他们的名字。
Stack Overflow用户
发布于 2013-05-03 00:15:01
SELECT a.*
FROM Movie a
INNER JOIN Casts b
ON a.ID = b.mID
INNER JOIN Actor c
ON b.pid = c.ID
LEFT JOIN
(
SELECT aa.mid
FROM Casts aa
INNER JOIN Actor bb
ON aa.pid = bb.ID
WHERE bb.fName = 'Y_Name'
) d ON a.id = d.mid
WHERE c.fname = 'X_Name' AND
d.mid IS NULL- SQLFiddle Demo
对子查询进行额外联接的原因是，我们正在按执行元的名称过滤记录。
假设你有这些记录
演员
╔════╦════════╗
║ ID ║ FNAME ║
╠════╬════════╣
║ 1 ║ X_Name ║
║ 2 ║ Y_Name ║
╚════╩════════╝电影
╔════╦══════╗
║ ID ║ NAME ║
╠════╬══════╣
║ 1 ║ Mov1 ║
║ 2 ║ Mov2 ║
║ 3 ║ Mov3 ║
╚════╩══════╝投射
╔═════╦═════╗
║ PID ║ MID ║
╠═════╬═════╣
║ 1 ║ 1 ║ <<== EXPECTED OUTPUT since Y_NAME is not present
║ 1 ║ 2 ║ on Movie Mov1
║ 2 ║ 2 ║
║ 1 ║ 3 ║
║ 2 ║ 3 ║
╚═════╩═════╝对输出执行操作
╔════╦══════╗
║ ID ║ NAME ║
╠════╬══════╣
║ 1 ║ Mov1 ║
╚════╩══════╝页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16342681
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号