mysql“哪里不在”使用两列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8435107/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql "Where not in" using two columns
提问by PFranchise
I have one temporary table that contains userIDand taskID. It is called CompletedTasks.
I have a second table that contains userIDand taskID. It is called PlannedTasks.
我有一张包含userID和 的临时表taskID。它被称为CompletedTasks。
我有一个包含userID和的第二个表taskID。它被称为PlannedTasks。
I need to get a list of all taskIDs that were completed, but not planned.
So, I need to somehow weed out from completed tasks, all rows where both:
我需要获得所有taskID已完成但未计划的列表。
所以，我需要以某种方式从已完成的任务中剔除所有行：
PlannedTasks.userID != CompletedTasks.userID
AND
和
PlannedTasks.taskID != CompletedTasks.taskID
回答by ypercube??
You can use this (more compact syntax):
你可以使用这个（更紧凑的语法）：
SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
( SELECT userID, taskID
FROM PlannedTasks
) ;
or the NOT EXISTSversion (which although more complex, should be more efficient with proper indexes):
或NOT EXISTS版本（虽然更复杂，但使用适当的索引应该更有效）：
SELECT c.*
FROM CompletedTasks AS c
WHERE NOT EXISTS
( SELECT 1
FROM PlannedTasks AS p
WHERE p.userID = c.userID
AND p.taskID = c.taskID
) ;
and of course the LEFT JOIN / IS NULLversion that @jmacinnes has in his answer.
当然还有LEFT JOIN / IS NULL@jmacinnes 在他的回答中的版本。
回答by jmacinnes
Is this what you need?
这是你需要的吗？
select ct.* from
completedTasks ct
left outer join plannedTasks pt on ct.taskId = pt.TaskId and ct.userId = pt.userId
where pt.taskId is null
However, I agree with the comment - given what we know from the question a status column sounds like a better schema than two tables.
但是，我同意评论 - 鉴于我们从问题中了解到的信息，状态列听起来比两个表更好的模式。
回答by Jagdeep Singh
@ypercube?? Thanks for sharing below mention query
@ypercube？？感谢分享下面提到的查询
SELECT * FROM CompletedTasks WHERE (userID, taskID) NOT IN
( SELECT userID, taskID FROM PlannedTasks) ;'
My problem solved.
我的问题解决了。
回答by M Silva
the first answer pretty good, It did work for me, just was missing a ")" after PlannedTasks. I need to weed out the elements from one table, that were in the other, so...
第一个答案非常好，它确实对我有用，只是在 PlannedTasks 之后缺少一个“）”。我需要从一张表中清除另一张表中的元素，所以...
SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
( SELECT userID, taskID
FROM PlannedTasks )
) ;
This is my code:
这是我的代码：
$query_C_Ranking = sprintf("SELECT *
FROM tblpinturas
WHERE (idCode) NOT IN
(SELECT idCode FROM tblranking)
");
