How does mysql find items that have not been sold in a certain period of time

  • 2020-06-07 05:26:37
  • OfStack

Front time, the roommate brought a question about mysql query:

There are three tables:

zp_id Primary key (Product id) inputtime(Product release time)

zp_id Foreign key (Product id) zo_id primary key (Order id) zo_voer_time (Order completion time)

3, zd_ord_pro Product Order Form zo_id Foreign key (Order id) zp_id (Product id)

Q: Use these three tables to find products that have not been sold in the first month?

And I'm not going to talk about how we talked about it, but I'm going to talk about it:

1. First, find the product id sold in this month, and find the data: data (table zd_order)

2. Continue to query zd_order table with the data found in step 1, but add not in (data)

3. Through the first two steps, you have found the product id that has not been sold, and then you can look up whatever you want.

select * from zd_product a1 left join zd_ord_pro a3 on a1.zp_id=a3.zp_id where a1.zp_id in (select zp_id from zd_order where a1.zp_id not in (select zp_id from zd_order a2 where a2.zo_voer_time>a1.inputtime and a2.zo_voer_time<a1.inputtime+30)) 

This statement is not verified, if there is an error, please modify.

Tip: Time addition needs to be converted to a timestamp.

Related articles: