Distinct MySQL filtering duplicate records

Go

Distinct this keyword to filter out redundant duplicate records only retains one, but it is often used only to return the number of distinct records, rather than using it to return all values that are not re-recorded. The reason is that distinct can only return its target field, and can not return other fields, and then through this article to share the use of SQL distinct, the need for friends can refer to the following

When using MySQL, it is sometimes necessary to query for a record that does not duplicate a field, although MySQL provides the DISTINCT keyword to filter out redundant duplicate records to keep only one, but it is often used only to return the number of distinct records, instead of using it to return all values that are not re-recorded. The reason is that distinct can only return its target field, and can not return other fields, this problem has plagued me for a long time, with distinct can not solve the words, I only use two-cycle query to solve, and so for a very large number of stations, will undoubtedly directly affect the efficiency of the So I wasted a lot of time.

In the table, duplicate values may be included. That's not a problem, but sometimes you might want to just list different values (distinct). Keyword DISTINCT is used to return only different values.

Table A:

Example 1

Copy CodeThe code is as follows:
Select distinct name from A

The following results are performed:

Example 2

Copy CodeThe code is as follows:
Select DISTINCT name, ID from A

The following results are performed:

In fact, according to "Name+id" to come and go, distinct at the same time on the name and ID , this way access and SQL Server support.

Example 3: Statistics

Copy CodeThe code is as follows:
Select COUNT (distinct name) from A; --the number of name to go back in the table, SQL Server support, and access does not support
Select COUNT (distinct name, id) from A; --sql server and access do not support

Example 4

Copy CodeThe code is as follows:
Select ID, distinct name from A; --The error will be indicated because the distinct must be placed at the beginning

Other

The field selected in the distinct statement can only be a field specified by distinct, and other fields are not possible. For example, if Table A has a memo column, if you want to get Distinc name and the corresponding Memo field, it is not possible to go directly through distinct.

However, there are other ways to implement a discussion of how SQL Server will stitch up multiple lines of content into one row

Distinct MySQL filtering duplicate records