How to use distinct in mysql

This article introduces how to use distinct in mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

When using mysql, it is sometimes necessary to query out records that do not repeat in a certain field. Although mysql provides the keyword distinct to filter out redundant duplicate records and retain only one record, it is often used only to return the number of records that do not repeat, rather than to return all the values of records that do not repeat. The reason is that distinct can only return its target field, not other fields.

Let's take a look at the example:

Table

Id name

1 a

2 b

3 c

4 c

5 b

For example, if I want to query all the data that name does not repeat with one statement, I must use distinct to remove the redundant duplicate records. Www.2cto.com

Select distinct name from table

The results are as follows:

Name

A

B

C

It seems to have achieved results, but what I want to get is the id value? Change the query statement:

Select distinct name, id from table

The result will be:

Id name

1 a

2 b

3 c

4 c

5 b

After trying for a long time, I finally found a usage in the mysql manual.

Www.2cto.com

Use group_concat (distinct name) with group by name to achieve the function I need 5.0 to support.

Suddenly, a flash of inspiration, since you can use the group_concat function, then other functions can work?

Try the count function quickly and successfully. Now release the complete statement:

Select *, count (distinct name) from table group by name

Results:

Id name count (distinct name)

1 a 1

2 b 1

3 c 1

On how to use distinct in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.