mysql“不在哪里"使用两列 [英] mysql "Where not in" using two columns

问题描述

我有一个包含userID和taskID的临时表.它称为CompletedTasks.
我还有另一个包含userID和taskID的表.它称为PlannedTasks.

I have one temporary table that contains userID and taskID. It is called CompletedTasks.
I have a second table that contains userID and taskID. It is called PlannedTasks.

我需要获取已完成但尚未计划的所有taskID的列表.
因此，我需要以某种方式从已完成的任务中清除所有行，同时都行:

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:

PlannedTasks.userID != CompletedTasks.userID 

AND

PlannedTasks.taskID != CompletedTasks.taskID

推荐答案

您可以使用此语法(更紧凑的语法):

You can use this (more compact syntax):

SELECT *
FROM CompletedTasks
WHERE (userID, taskID) NOT IN
      ( SELECT userID, taskID
        FROM PlannedTasks
      ) ;

或NOT EXISTS版本(虽然更复杂，但使用适当的索引应该更有效):

or the NOT EXISTS version (which although more complex, should be more efficient with proper indexes):

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
      ) ;

，当然还有@jmacinnes在回答中使用的LEFT JOIN / IS NULL版本.

and of course the LEFT JOIN / IS NULL version that @jmacinnes has in his answer.

这篇关于mysql“不在哪里"使用两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！