MySQL JOIN 两个表查询

Question

我有两个具有这些结构的表：

1. 客户

   • id_customer
   • customer_name
   • customer_email
   • customer_address

2. 订单

   • id_order
   • order_number
   • customer_id
   • order_price
   • 付款
   • order_date

我需要一个查询，它为我提供了来自"客户"的所有"id_customer"，这些"客户"位于表"订单"/"customer_id"中，其中"order_price">"付款"和"odrer_date"至少在 30 天前

Answer 1

SQL的核心是一种操作集合的方法。从子查询开始，以确定customer_id值集与orders表中的条件匹配。

SELECT DISTINCT customer_id
FROM orders
WHERE order_price > payment
AND order_date <= CURDATE() - INTERVAL 30 DAY

然后，使用该子查询从customers表中获取数据。

SELECT *
FROM customers
WHERE id_customer IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_price > payment
AND order_date <= CURDATE() - INTERVAL 30 DAY
) 

Answer 2

使用此查询

Select * from customers c inner join orders o On c. id_cusomer = o.customer_id 
where o.order_price > o.payment and o.order_date = CURDATE() - INTERVAL 30 DAY