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

This article mainly introduces "the use of distinct in SQL". In the daily operation, I believe that many people have doubts about the use of distinct in SQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the use of distinct in SQL". Next, please follow the editor to study!

Let's take a look at the example:

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 works, but it works in two fields at the same time, that is, it must have the same id and name before it 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's as simple as that.

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

At this point, the study of "how to use distinct in SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.