MySQL - 从表的id字段中没有对应项的数字列表中选择

我有一个数字列表,比如{2,4,5,6,7}我有一张表,foos,有foos.ID,包括说,{1,2,3,4,8,9}

我喜欢拿我的数字列表,并在我的表的ID字段中找到没有对应的数字.

实现此目的的一种方法是创建一个表格栏,在ID字段中加载{2,4,5,6,7}.然后,我会这样做

SELECT bars.* FROM bars LEFT JOIN foos ON bars.ID = foos.ID WHERE foos.ID IS NULL

但是,我想完成这个没有临时表.

任何人都对如何发生有任何意见？

这是一个非常常见的问题:在不创建表的情况下即时生成关系.针对这个问题的SQL解决方案非常尴尬.使用派生表的一个示例:

SELECT n.id
FROM
  (SELECT 2 AS id 
   UNION SELECT 3 
   UNION SELECT 4 
   UNION SELECT 5 
   UNION SELECT 6 
   UNION SELECT 7) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

但这不能很好地扩展,因为你可能有很多值,而不是只有六个.构建一个UNION每个值所需的长列表可能会很烦人.

另一种解决方案是保留一个十位数的通用表,并将其重复用于多种用途.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT n.id
FROM 
  (SELECT n1.i + n10.i*10 AS id
   FROM num AS n1 CROSS JOIN num AS n10
   WHERE n1.i + n10.i*10 IN (2, 3, 4, 5, 6, 7)) AS n
  LEFT OUTER JOIN foos USING (id)
WHERE foos.id IS NULL;

我显示内部查询从0..99生成值,即使这对于这种情况不是必需的.但是列表中的值可能大于10.关键是,使用一个表num,您可以生成大数字,而不必求助于UNION每个值一个非常长的链.此外,您可以在一个位置指定所需值的列表,这样更方便和可读.

我找不到不使用临时表的精确问题的解决方案,但使用子选择而不是连接进行查询的另一种方法是:

SELECT bars.* FROM bars WHERE bars.ID NOT IN (SELECT ID FROM foos)

像我最初写的其他海报一样:

SELECT * FROM foos WHERE foos.ID NOT IN (2, 4, 5, 6, 7)

但后来我意识到这与你想要的相反.

如果使用PHP,则可以在不创建任何临时表的情况下完成此工作.

SELECT ID FROM foos WHERE foos.ID IN (2, 4, 5, 6, 7)

您可以使用PHP的array_diff()函数将其转换为所需的结果.如果您的列表(2,4,5,6,7)位于名为$ list的数组中,并且上面查询的结果是数组$ result,那么

$no_counterparts = array_diff($list, $result);

...将返回列表中的所有数字,而数据库表中没有对应的数字.虽然此解决方案不会在查询中执行整个操作,但您需要在PHP中执行的后处理是最小的,以获得您想要的内容,并且可能值得避免必须创建临时表.