A solution for querying multiple non duplicate record values in mysql using distinct
- 2020-05-06 11:44:10
- OfStack
When using mysql, it is sometimes necessary to query for records that are not duplicated in a field. Although mysql provides the keyword distinct to filter out redundant duplicate records and keep only one, it is often used to return the number of non-duplicate records rather than all values that are not duplicated. The reason is that distinct can only return its target field and cannot return other fields. This problem has troubled me for a long time. If distinct cannot solve it, I have to solve it by double-loop query. So I spent a lot of time researching this problem, and I couldn't find a solution on the Internet, so I brought rong rong to help, and we were both depressed...
Here's an example:
table
id name
1 a
2 b
3 c
4 c
5 b
The library structure looks something like this. This is just a simple example.
For example, if I want to use a single statement to query all the data that name does not repeat, then I must use distinct to remove the redundant duplicate records.
select distinct name from table
The result:
name
a
c
That seems to work, but what I want is id? Change the query:
select distinct name, id from table
The result:
id name
1 a
2 b
3 c
4 c
5 b
Why didn't distinct work? It works, but it works on two fields at the same time, so it must be the same as id and name to be excluded...
Let's change the query:
select id, distinct name from table
Unfortunately, you get nothing but error messages, distinct must be at the beginning. Too hard to put distinct in where condition? Yes, just the same...
Isn't it troublesome? Indeed, efforts were made to solve the problem. No way. Keep asking.
After he showed me distinct in oracle, he could not find the solution in mysql. He suggested me to try group by before leaving work.
Finally, I found a usage in the mysql manual. group_concat(distinct name) and group by name realized the function I needed.
Report an error... Depressed... Even the mysql manual is against me. First it gives me hope, then it pushes me to disappointment.
On closer inspection, the group_concat function is 4.1 support, halo, I 4.0. No way, upgrade, level one try, success...
Finally, it's done, but then you have to ask the customer to upgrade.
Now that you can use the group_concat function, does anything else work?
Just try count. It works. I... I want to cry, after all this work... It's that simple...
Now put out the full 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 one is redundant. Just ignore it.
Alas, the original mysql so stupid, gently cheated him past, depressed I also (right, and the guy), now take out hope that you don't be this problem.
Oh, yes, and by the way, group by must be placed before order by and limit, otherwise there will be an error.
-----------------------------------------------------------------------------------------
More depressing things happen, and as you prepare to submit, you'll see that there's an easier solution...
select id, name from table group by name
It seems that the understanding of mysql is still too superficial, not afraid of being laughed at, sent out to let everyone not to make the same mistake...
by sol
Here's an example:
table
id name
1 a
2 b
3 c
4 c
5 b
The library structure looks something like this. This is just a simple example.
For example, if I want to use a single statement to query all the data that name does not repeat, then I must use distinct to remove the redundant duplicate records.
select distinct name from table
The result:
name
a
c
That seems to work, but what I want is id? Change the query:
select distinct name, id from table
The result:
id name
1 a
2 b
3 c
4 c
5 b
Why didn't distinct work? It works, but it works on two fields at the same time, so it must be the same as id and name to be excluded...
Let's change the query:
select id, distinct name from table
Unfortunately, you get nothing but error messages, distinct must be at the beginning. Too hard to put distinct in where condition? Yes, just the same...
Isn't it troublesome? Indeed, efforts were made to solve the problem. No way. Keep asking.
After he showed me distinct in oracle, he could not find the solution in mysql. He suggested me to try group by before leaving work.
Finally, I found a usage in the mysql manual. group_concat(distinct name) and group by name realized the function I needed.
Report an error... Depressed... Even the mysql manual is against me. First it gives me hope, then it pushes me to disappointment.
On closer inspection, the group_concat function is 4.1 support, halo, I 4.0. No way, upgrade, level one try, success...
Finally, it's done, but then you have to ask the customer to upgrade.
Now that you can use the group_concat function, does anything else work?
Just try count. It works. I... I want to cry, after all this work... It's that simple...
Now put out the full 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 one is redundant. Just ignore it.
Alas, the original mysql so stupid, gently cheated him past, depressed I also (right, and the guy), now take out hope that you don't be this problem.
Oh, yes, and by the way, group by must be placed before order by and limit, otherwise there will be an error.
-----------------------------------------------------------------------------------------
More depressing things happen, and as you prepare to submit, you'll see that there's an easier solution...
select id, name from table group by name
It seems that the understanding of mysql is still too superficial, not afraid of being laughed at, sent out to let everyone not to make the same mistake...
by sol