mysql - 如何获取没有发票号的订单号

Question

如何找到没有发票号码的订单号？我做了以下查询，它显示了所有有发票号的订单。如何获取所有没有发票号的订单号？

SELECT invoice_tbl.increment_id AS 'Sales_Invoice_Number', order_tbl.increment_id AS   'Sales Order Number'
FROM magentodb.sales_flat_invoice AS invoice_tbl
JOIN magentodb.sales_flat_order AS order_tbl
  ON (order_tbl.entity_id = invoice_tbl.order_id)
WHERE order_tbl.created_at >= '2014-03-17'
  AND order_tbl.created_at <= '2014-05-07'

Answer 1

您可以使用 NOT EXISTS 子句。

SELECT  order_tbl.increment_id AS   'Sales Order Number'
FROM  magentodb.sales_flat_order AS order_tbl

WHERE order_tbl.created_at >= '2014-03-17'
  AND order_tbl.created_at <= '2014-05-07'
  AND NOT EXISTS (SELECT NULL FROM magentodb.sales_flat_invoices
                  WHERE order_tbl.entity_id = order_id)

sales_flat_invoice 上的 LEFT JOIN使用 where 子句检查 null order_id 也可以达到目的。