Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to query multiple non-duplicate record values in MySQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Table

Id name

1 a

2 b

3 c

4 c

5 b

The structure of the library is something like this, this is just a simple example, the actual situation will be much more complicated.

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.

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

Why didn't distinct work? It worked, but he used two fields at the same time, that is, only those with the same id and name can be excluded.

Let's change the query statement again:

Select id, distinct name from table

Unfortunately, you get nothing but error messages, distinct must be put at the beginning. Is it too difficult to put distinct in the where condition? If you can, you can still report an error.

Isn't it troublesome? Indeed, no effort has been made to solve the problem. I can't help it. Keep asking.

Hold up a JAVA programmer in the company. He showed me how to use distinct in oracle, but couldn't find a solution in mysql. Finally, he suggested that I try group by before leaving work.

Tried for a long time, and finally found a usage in the mysql manual, using group_concat (distinct name) with group by name to achieve the function I need, excited, God bless me, try as soon as possible.

Report an error. Depressed. Even the mysql manual also had a problem with me, first gave me hope, and then pushed me to disappointment.

Check again, group_concat function is 4. 1 support, halo, mine 4. 0. There is no way, upgrade, upgrade a try, success.

Finally, it is done, but in this way, the customer must also be asked to upgrade.

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

Quickly use the count function to try, success, I. I want to cry. I've spent so much time. It turns out it's that simple.

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

The last item is superfluous, don't worry about it, the goal is achieved.

Alas, it turned out that mysql was so stupid that he fooled him at once, and I was the only one who was depressed (by the way, there was room for that guy). Now I hope everyone won't be bothered by this problem.

Oh, yes, and by the way, group by must be put before order by and limit, or there will be an error.

Something more depressing has happened, and when you are ready to submit, allow to find that there is a simpler solution:

Select id, name from table group by name

After reading the above, do you have any further understanding of how to query multiple non-repeating record values in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.