Detailed example of using the distinct method in MySQL

A distinct

Meaning: distinct is used to query the number of unique records, that is, distinct returns the number of unique fields (count(distinct id)). The reason is that distinct can only return its target field, but not other fields.

Usage Notes:

1.distinct [query field] must be placed at the beginning of the field to be queried, that is, in the first parameter;
2. It can only be used in SELECT statements, not in INSERT, DELETE, or UPDATE;
3.DISTINCT means to concatenate all the following parameters to get non-duplicate records, that is, each row of concatenated parameters is unique.
4. It cannot be used together with all. By default, all results are returned during the query.

1.1 Check only one field

Checking for duplicates in a field means selecting a column of non-duplicate data in that field.

Example table: psur_list

To remove duplicates from the PLAN_NUMBER field, use the following statement:

SELECT DISTINCT PLAN_NUMBER FROM psur_list;

The results are as follows:

1.2 Multiple fields deduplication

De-duplicate multiple fields, which means selecting a record that is a concatenation of multiple fields, and all non-duplicate records

Example table: psur_list

To remove duplicates from the PLAN_NUMBER and PRODUCT_NAME fields, use the following statement:

SELECT DISTINCT PLAN_NUMBER,PRODUCT_NAME FROM psur_list;

The results are as follows:

Expected result: Only the first parameter PLAN_NUMBER has a unique value

Solution 1: Use the group_concat function

Statement:

SELECT GROUP_CONCAT(DISTINCT PLAN_NUMBER) AS PLAN_NUMBER,PRODUCT_NAME FROM psur_list GROUP BY PLAN_NUMBER

Solution 2: Use group by

Statement:

SELECT PLAN_NUMBER,PRODUCT_NAME FROM psur_list GROUP BY PLAN_NUMBER

The results are as follows:

1.3 Dealing with null

distinct will not filter out null values, and the returned results will include null values

The table psur_list is as follows:

To remove duplicates from the COUNTRY field, use the following statement:

SELECT DISTINCT COUNTRY FROM psur_list

The results are as follows:

1.4 Synonymous with distinctrow

Statement:

SELECT DISTINCTROW COUNTRY FROM psur_list

The results are as follows:

Using distinct in binary aggregation functions

In aggregate functions, DISTINCT is usually used in conjunction with COUNT. count() will filter out null items

Statement:

SELECT COUNT(DISTINCT COUNTRY) FROM psur_list

The results are as follows: [There are actually 4 records containing null items. After executing the statement, the null items are filtered out and the calculation is 3]

This is the end of this article about the detailed example of using the distinct method in MySQL. For more relevant MySQL deduplication distinct content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!