How does mysql query products not sold in a certain period of time?

Source: Internet
Author: User

At the front-end time, my roommate asked me a question about mysql query:

There are three tables:

1. zd_product table zp_id primary key (product id) inputtime (product release time)

2. zd_order order table zp_id foreign key (product id) zo_id primary key (Order id) zo_voer_time (order completion time)

3. zd_ord_pro product order table zo_id foreign key (Order id) zp_id (product id)

Q: Can I use these three tables to find products that have been sold since the product was released to "NONE" within a month?

Here I will not talk about how we have discussed it. Let's look at it directly:

1. First, search for the product id that was sold in January and the data: data (the table named zd_order)

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

3. In the first two steps, you have found the product id that has not been sold. Then you can check the product id if you want.

Statement:
Copy codeThe Code is as follows:
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 any error occurs, modify it.

Tip: The time addition must be converted to the timestamp.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.