SQL language: how to select one from multiple records?

Source: Internet
Author: User

Recently, csdn has found many questions about this problem. I encountered this problem when I was writing a program, but it has already been solved. Now I will explain how to solve this problem.

Example:

Two tables, named corp_info, with the field ID, name, type ,......, The other is product, with the field name ID, product_name, date, ammount ,...... . In these two tables, the ID of the table corp_info is the primary key, and the product table indicates how many products a company has bought at a certain time. The problem is to list the information about the products recently purchased by each company.

Solution:

First, we need to list the purchase information of each company. This is very simple. The SQL statement is as follows:

Select corp_info.name, product. * From corp_info, product where corp_info.id = product. ID;

Secondly, we need to select the largest purchase date record of each company from these records and use the Aggregate Function

Select a. Name, C. product_name.c.date, C. ammount from corp_info,

(

Select Product. ID, max (date) as last_date from corp_info, product where corp_info.id = product. ID

Group by product. ID -- select the maximum date in the purchase information of the same company.

) B,

Product c

Where a. ID = B. ID and B. ID = C. ID and C. ID = A. ID and C. last_date = B. Date

In this way, we can select a task from multiple records.

Similarly, if the purchase information of the same company within the same date exists, then we still need to aggregate ammount, product_name, and even both, depending on the nature of the subsequent table.

 

 

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.