SQL language: how to select one from multiple records?

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.