MySQL 8.0 -选择查询

Question

嗨，我在用我的表构建一个select查询命令时遇到了问题。

表客户

+-----------------+-----------------+--------------+
| customer_number | customer_status | order_status |
+-----------------+-----------------+--------------+
|     196         |      unpaid     |    served    |
+-----------------+-----------------+--------------+
|     197         |      unpaid     |  unserved    |
+-----------------+-----------------+--------------+
|     198         |       paid      |    served    |
+-----------------+-----------------+--------------+

表CUSTOMER_ORDERS

+-----------------+-----------------+--------------+
| cust_ord_number | customer_number | order_status |
+-----------------+-----------------+--------------+
|       350       |       196       |  preparing   |
+-----------------+-----------------+--------------+
|       351       |       196       |   pending    |
+-----------------+-----------------+--------------+
|       352       |       197       |   pending    |
+-----------------+-----------------+--------------+
|       353       |       197       |   pending    |
+-----------------+-----------------+--------------+
|       354       |       198       |   prepared   |
+-----------------+-----------------+--------------+
|       355       |       198       |   prepared   |
+-----------------+-----------------+--------------+

我想要的是select所有的customer_number，它的customer_status是未支付的，而order_status没有从table customer获得服务，customer_number不能拥有来自customer_orders的order_status preparing或prepared。

基本上，我需要选择所有的customer_number，是无报酬和不服务的，不能有准备或准备。

对不起，如果我没有很好地解释它，任何答案都会有帮助，谢谢。

Answer 1

SELECT *
FROM customer c
LEFT JOIN customer_orders co 
    ON c.customer_number = co.customer_number 
   AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid'
  AND c.order_status = 'unserved'
  AND co.cust_ord_number IS NULL