SQL de-weight

Distinct: Used to return only a different value.

Note: 1. The Select field in the statement can only be the field specified by distinct, and the other fields cannot be displayed.

2. Distinct must be placed at the beginning such as select distinct name from table

GROUP BY: "Grouping (group) according to certain rules (by)"

Its function is to divide a data set into several small areas by certain rules and then data processing for several small regions.
Note: 1. GROUP by the specified column, which must contain a clustered function such as (AVG, MAX, MIN, SUM, COUNT) in the query result

2, group by IS first sorted after grouping;

Form Name table

ID Name Address Telephone

1 Zhang three Harbin 123456789

2 John Doe Shanghai 784512357

3 Harry Beijing 759878456

4 Zhang San Hong Kong 785641231

ID unique, remove name duplicate records. Reserved with the largest ID, the following table is generated:

ID Name Address Telephone

2 John Doe Shanghai 784512357

3 Harry Beijing 759878456

4 Zhang San Hong Kong 785641231

You need to display extra fields at this time so you can't use the DISTINCT method!!

Correct wording: Based on a single field (Name), only the record with the highest ID is retained, and then filtered

Select*from table where ID in

(select Max (ID) from table group by Name has count (Name) >0)

Incorrect notation: GROUP by has actually grouped name,address,telephone simultaneously

Select Max (ID), name,address,telephone from table group by Name,address,telephone

Show Results:

ID Name Address Telephone

1 Zhang three Harbin 123456789

2 John Doe Shanghai 784512357

3 Harry Beijing 759878456

4 Zhang San Hong Kong 785641231

SQL de-weight