Usage of mysql to remove DISTINCT keywords in repeated Select statements

Mysqldistinct: Usage of distinct in mysql. how can I remove duplicates in mysql ?, In PHPdistinct usage, how does PHP remove duplicates?

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 to return the number of records that do not repeat, instead of returning 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 this problem, I only need to use double loop query to solve it, this will undoubtedly directly affect the efficiency of a station with a large data volume. So I spent a lot of time studying this problem and I couldn't find a solution on the Internet.

Let's take a look at the example below:

Table
Id name
1
2 B
3 c
4 c
5 B

The library structure is like this. this is just a simple example, and the actual situation is much more complicated.

For example, if you want to use a statement to query all data with no duplicate names, you must use distinct to remove redundant duplicate records.

Select distinct name from table
The result is:

Name
A
B
C

It seems that the effect has been achieved, but what I want to get is the id value? Modify the query statement:

Select distinct name, id from table

The result is:

Id name
1
2 B
3 c
4 c
5 B

How does distinct not work? It works, but it also applies to two fields, that is, it must have the same id and name to be excluded .......

Modify the query statement again:

Select id, distinct name from table

Unfortunately, you cannot get anything except the error message. you must start with distinct. Is it difficult to place distinct in the where condition? Yes. an error is reported as a result .......

Very troublesome? Indeed, this problem cannot be solved with all the effort. No way. continue to ask.

He grabbed a JAVA programmer in the company and showed me the solution in mysql after using distinct in oracle. before leaving work, he suggested that I try group.

I tried it for a long time, and I couldn't do it. I finally found a usage in the mysql Manual. I realized what I needed with group_concat (distinct name) and group by name, try it now.

Error ............ Depressed ....... I can't even go through the mysql Manual. I gave me hope first, and then pushed me to disappointment ....

Check again. The group_concat function is supported by 4.1, dizzy. I have 4.0. No way. Upgrade. the upgrade is successful ......

Finally, the customer must be asked to upgrade.

Suddenly, the ghost machine flashed. since the group_concat function can be used, can other functions be used?

Use the count function to try it out. I am a success ....... It takes so much time to cry ........ It turns out to be so simple ......

Now release the complete statement:

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

Result:

Id name count (distinct name)
1 a 1
2 B 1
3 c 1

The last item is redundant, so you don't have to worry about it. The goal is achieved .....

Alas, it turned out that mysql was so stupid that I would just lie to him with a single click. I am so depressed. now I hope you will not be overwhelmed by this problem.

Oh, yes. by the way, group by must be placed before order by and limit. Otherwise, an error will be reported ........! OK