In SQL, the simplest implementation method is to take only one entry for multiple identical entries.

In SQL, the simplest implementation method is to take only one entry for multiple identical entries.

In SQL, the following situations are often encountered: two records in a table are basically the same, and one or more fields are slightly different,

At this time, we may need to kick out the data with the difference, that is, only one of two or more records is retained.

Table timeand

For the case where the time field is the same, the total and name are different. If the time field is the same, only one of the data is taken. The simplest implementation method is either of the two.

1. select time, max (total) as total, name from timeand group by time; // obtain the maximum value of total in the record

Or select time, min (total) as total, name from timeand group by time; // obtain the minimum value of total in the record

The two solutions both have the disadvantage that the content of the name field cannot be distinguished. Therefore, it is generally used when only two fields or other fields have the same content.

2. select * from timeand as a where not exists (select 1 from timeand where a. time = time and a. total <total );

In this solution, the name field in solution 1 is inaccurate, and the maximum value of total is obtained.

In the above example, only one field is different. What if two fields are the same? What should I do if I want to investigate the maximum value of the third field?

In fact, it is very simple. Just make a slight modification on the basis of the original:

Original SQL statement:

Select * from timeand as a where not exists (select 1 from timeand where a. time = time and a. total <total );

It can be changed:

Select * from timeand as a where not exists (select 1 from timeand where. time = time and (. total <total or (. total = total and. outtotal <outtotal )));

Outtotal is another field of the Int type.

The above is all the content of the simplest implementation method that only takes one piece of the same content in SQL. I hope to give you a reference and support for the help house.