Shulou(Shulou.com)06/01 Report--

This article mainly introduces the use of the distinct keyword in SQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

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 are not duplicated. The reason is that distinct can only return its target field, but can not return other fields, this problem has perplexed me for a long time. If I can't solve it with distinct, I can only use double loop query to solve it. For a station with a very large amount of data, it will undoubtedly directly affect the efficiency. So I spent a lot of time studying this problem, and I couldn't find a solution online. During that time, I pulled Rong Rong to help. As a result, both of us were depressed.

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

The above is all about the use of the distinct keyword in SQL, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.