Four uses of the distinct keyword in SQL

This article brings you the four usages (code examples) of the distinct keyword in SQL. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

When using mysql, sometimes you need to query for records with unique fields. Although mysql provides the distinct keyword to filter out redundant duplicate records and keep only one, it is often used to return duplicate records. The number of duplicate records instead of using it to return all values ​​without duplicate records. The reason is that distinct can only return its target field, but cannot return other fields. This problem has troubled me for a long time. If it cannot be solved with distinct, I can only solve it with a double loop query, which is very large for a large amount of data. For the website, it will undoubtedly directly affect the efficiency. So I spent a lot of time researching this problem, and couldn't find a solution online. During this period, I roped in Rong Rong to help. As a result, both of us were depressed.

Let's take a look at an example first:

table

id name

1 a

2 b

3 c

4 c

5 b

The library structure is roughly like this. This is just a simple example. The actual situation will be much more complicated.

For example, if I want to use one statement to query all data with non-duplicate names, I must use distinct to remove redundant duplicate records.

select distinct name from table

The result obtained is:

name

a

b

c

It seems to have achieved the effect, 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 doesn’t distinct work? It works, but it affects two fields at the same time, that is, the id and name must be the same to be excluded. . . . . . .

Let’s change the query statement again:

select id, distinct name from table

Unfortunately, you can’t get anything except error information, distinct must be placed at the beginning . Is it so difficult to put distinct in the where condition? Yes, it still reports an error. . . . . . .

Is it troublesome? Indeed, all efforts have failed to solve this problem. I have no choice but to continue asking someone.

I grabbed a JAVA programmer in the company and showed me how to use distinct in Oracle, but he couldn't find a solution in MySQL. Finally, before leaving get off work, he suggested that I try group by.

After trying for a long time, it didn’t work. Finally, I found a usage in the mysql manual. I used group_concat(distinct name) with group by name to achieve the function I needed. I am excited and God bless me. I will give it a try as soon as possible.

Error reporting. . . . . . . . . . . . depressed. . . . . . . Even the mysql manual couldn't get along with me. It first gave me hope, and then pushed me into despair. It was so cruel. . . .

After careful inspection, the group_concat function is supported by 4.1, halo, I am 4.0. There is no other way, upgrade, try after upgrading, success. . . . . .

Finally got it done, but then we have to ask customers to upgrade as well.

Suddenly an idea flashed. Since the group_concat function can be used, can other functions also work?

Hurry up and try the count function, it works, me. . . . . . . I want to cry, it took so much effort. . . . . . . . It turns out it's that 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 above is the detailed content of Four uses of the distinct keyword in SQL. For more information, please follow other related articles on the PHP Chinese website!