mysql - 查找不匹配的记录mysql

Question

我有一张 Orders 表，其中包含 OrderId 和 OrderName 列。

OrderId  |    OrderName
------------------------
  1      |          food
-------------------------
  2      |          milk
----------------------
  3      |     veg
----------------------
  4      |     banana
------------------------

我想匹配订单 ID 和一些随机订单 ID，例如 3,5,6,2,1。我只想要不匹配的订单 ID，例如 5,6。请帮我查询。

谢谢。

Answer 1

因为您不想获取订单 ID 5 和 6，所以您可以通过以下查询排除这些订单-

SELECT * 
FROM Orders 
WHERE OrderID NOT IN(5,6);

根据用户要求更新:

    SELECT a.myid as OrderId FROM 
    (
SELECT a.m*10000+b.n*1000+c.p*100+d.q*10+e.r AS  myid FROM 
(SELECT 0 m UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
JOIN 
(SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b 
JOIN 
(SELECT 0 p UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c 
JOIN 
(SELECT 0 q UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d 
JOIN 
(SELECT 0 r UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e 
ORDER BY myid
    ) a 
    LEFT JOIN orders b ON a.myid=b.orderid 
    WHERE a.myid IN (1,2,3,11,12,6) AND b.orderid IS NULL;

只需将您的订单列表放入 a.myid() 并执行上面的查询。

进一步如果您自己的订单更多，那么我们可以相应地修改查询。