mysql - 我的sql查询有什么问题

Question

我希望它返回 products_extrafield_id 不是 14 的记录，但它仍然在我的结果中返回它..我正在使用连接

SELECT op. * 
FROM 
  products_to_products_extra_fields AS p
  INNER JOIN orders_products AS op ON p.products_id = op.products_id
  INNER JOIN orders AS o ON op.orders_id = o.orders_id
WHERE NOT 
EXISTS (

    SELECT * 
    FROM products_to_products_extra_fields
    WHERE
      p.products_id = op.products_id
      AND p.products_extra_fields_id = 14
  )
  AND o.date_purchased BETWEEN  '2013-11-29' AND  '2013-12-03 23:59:59'
  AND o.payment_method =  'Institutional Billing'
  AND o.orders_status <100001
GROUP BY o.orders_id
ORDER BY DECODE( o.cc_type,  'oFsAfHr7' ) ASC 
LIMIT 0 , 30

Answer 1

如果我正确地读取了您的 SQL，那么您不需要 NOT EXISTS 子句来执行此操作。只需将 NOT EXISTS 子句替换为以下语句:p.products_extra_fields_id != 14

SELECT 
    op. * 
FROM 
    products_to_products_extra_fields AS p
    INNER JOIN orders_products AS op 
    ON p.products_id = op.products_id
    INNER JOIN orders AS o 
    ON op.orders_id = o.orders_id
    WHERE 
        p.products_extra_fields_id != 14
    AND o.date_purchased BETWEEN  '2013-11-29' AND  '2013-12-03 23:59:59'
    AND o.payment_method =  'Institutional Billing'
    AND o.orders_status <100001
GROUP BY o.orders_id
ORDER BY DECODE( o.cc_type,  'oFsAfHr7' ) ASC 
LIMIT 0 , 30