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. During this period, I pulled Rong for help. As a result, both of us were depressed .........
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
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 just a few clicks. I am so depressed (by the way, there is also the guy Rong). 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, which is almost the same and sent to Rongrong website, I keep busy ......
Bytes -----------------------------------------------------------------------------------------
Even more depressing things happen. When preparing for submission, we can find a simpler solution ......
Select id, name from table group by name
It seems that the understanding of mysql is still too superficial, not afraid of getting jokes, so that everyone should not make the same mistake ......
By Sol