Usage of distinct in SQL (four examples) and sqldistinct

Usage of distinct in SQL (four examples) and sqldistinct

When using mysql, you sometimes need to query records with unique fields. Although mysql provides the keyword distinct to filter out redundant duplicate records, only one record is retained, but it is often used only to return the number of records that do not repeat, rather than to return all values that do not record the record. The reason is that distinct can only return its target field, but cannot return other fields. This problem has plagued me for a long time. If distinct cannot solve it, I only need to use double loop query to solve it, this will undoubtedly directly affect the efficiency of a station with a large amount of data, which wastes a lot of time.

Duplicate values may exist in the table. This is not a problem. However, sometimes you may want to list only different values. The keyword distinct is used to return a unique value.

Table:

Example 1

Copy codeThe Code is as follows:
Select distinct name from

The result is as follows:

 

Example 2

Copy codeThe Code is as follows:
Select distinct name, id from

The result is as follows:

It is actually based on"Name + id"Distinct also plays a role inName and id.Access and SQL ServerSupported at the same time.

Example 3: Statistics

Copy codeThe Code is as follows:
Select count (distinct name) from A; -- number of names deduplicated In the table, which is supported by SQL Server, but not by Access
Select count (distinct name, id) from A; -- neither SQL Server nor Access is supported.

Example 4

Copy codeThe Code is as follows:
Select id, distinct name from A; -- an error is prompted because distinct must start

Others

In the distinct statement, the select field can only be a field specified by distinct, and other fields cannot appear. For example, if Table A has A "Remarks" column, it is impossible to directly use distinct to obtain the distinc name and the corresponding "Remarks" field.

However, you can use other methods to discuss how SQL Server concatenates multiple rows in a column into one row.