The simplest way to achieve the same content in SQL is to take only one _mssql

SQL often encountered the following situation, in a table with two records basically exactly the same, one or several fields slightly different,

This may require us to kick out these differential data, that is, only one item is retained in two or more records.

As follows: Table Timeand

When the time field is the same, with a different total and name, the simplest way to implement it is to take only one of the same data when you encounter the same.

1. Select Time,max (total) as Total,name from Timeand Group by time;//take the largest value in the record

or select Time,min (total) as Total,name from Timeand Group by time;//take the smallest value in the record

Both of these scenarios have the disadvantage of not distinguishing the contents of the Name field, so it is generally used for situations where only two fields or other fields are identical

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

This scenario excludes the problem of inaccurate name fields in Scenario 1, which takes the maximum value of total

The above example is only one field is not the same, if two fields appear the same? Ask to investigate the maximum value of the third field how to do?

In fact it is very simple, on the original basis of a slight modification can be:

The original SQL statement:

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

Can be modified to:

SELECT * from Timeand as a where not exists (select 1 to timeand where a.time = time and (A.total<total or A.tota L=total and A.outtotal<outtotal ));

Where Outtotal is another field, the int type

The above is the SQL encountered many of the same content to take only one of the simplest way to achieve the full content of the method, I hope to give you a reference, but also hope that we support the cloud habitat community.