Parsing mysql does not repeat the field value summation
- 2020-05-24 06:21:47
- OfStack
When using mysql, it is sometimes necessary to query for a non-duplicate record in a field. Although mysql provides the keyword distinct to filter out redundant duplicate records and keep only one record, it is often used only to return the number of non-duplicate records, rather than all the values of non-duplicate records. The reason is that distinct can only return its target field, but cannot return other fields. This problem has been bothering me for a long time. If distinct cannot solve this problem, I have to use two circular queries to solve it. So I spent a lot of time researching this problem, and I couldn't find a solution online.
Here's an example:
table
id name
1 a
2 b
3 c
4 c
5 b
The library structure looks like this, this is just a simple example, the actual situation will be much more complex.
For example, if I want to query with one statement to get all the data that name does not repeat, then I must use distinct to get rid of the redundant duplicate records.
select distinct name from table
The result is:
name
a
c
It seems to work, but what I want to get is the id value? Change 1 to the following query statement:
select distinct name, id from table
The result would be:
id name
1 a
2 b
3 c
4 c
5 b
Why isn't distinct working? It works, but it also works on two fields, so you have to have the same id and the same name to be excluded...
Let's change the query statement:
select id, distinct name from table
Unfortunately, you get nothing but error messages, distinct must be at the beginning. It's too hard to put distinct in the where condition, right? Yes, still report an error...
Is it a hassle? It is true that efforts have failed to solve the problem. I can't help it. Keep asking.
He showed me the solution in mysql after using distinct in oracle. At last, he suggested me to try group by before going off work.
Finally, I found a usage in mysql manual, and used group_concat(distinct name) in conjunction with group by name to achieve the function I need. I'm excited, god help me, please try it.
Error reporting... Depressed... Even the mysql manual is against me. It gives me hope and then pushes me to despair.
On closer inspection, group_concat function is 4.1 support, halo, I 4.0. No way, upgrade, upgrade level 1 try, success...
Finally done, but so 1 to, and must ask the customer to upgrade.
Now that you can use the group_concat function, what about the other functions?
Try it with count function 1. Success. I... I want to cry, after all this time... It's that simple...
Now release 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 item is superfluous. Just ignore it.
Alas, it turns out that mysql is so stupid that he was cheated by a single blow. I am the only one who is depressed. I hope you won't be troubled by this problem.
Oh, yes, and by the way, group by must be placed before order by and limit, otherwise there will be an error.
Here's an example:
table
id name
1 a
2 b
3 c
4 c
5 b
The library structure looks like this, this is just a simple example, the actual situation will be much more complex.
For example, if I want to query with one statement to get all the data that name does not repeat, then I must use distinct to get rid of the redundant duplicate records.
select distinct name from table
The result is:
name
a
c
It seems to work, but what I want to get is the id value? Change 1 to the following query statement:
select distinct name, id from table
The result would be:
id name
1 a
2 b
3 c
4 c
5 b
Why isn't distinct working? It works, but it also works on two fields, so you have to have the same id and the same name to be excluded...
Let's change the query statement:
select id, distinct name from table
Unfortunately, you get nothing but error messages, distinct must be at the beginning. It's too hard to put distinct in the where condition, right? Yes, still report an error...
Is it a hassle? It is true that efforts have failed to solve the problem. I can't help it. Keep asking.
He showed me the solution in mysql after using distinct in oracle. At last, he suggested me to try group by before going off work.
Finally, I found a usage in mysql manual, and used group_concat(distinct name) in conjunction with group by name to achieve the function I need. I'm excited, god help me, please try it.
Error reporting... Depressed... Even the mysql manual is against me. It gives me hope and then pushes me to despair.
On closer inspection, group_concat function is 4.1 support, halo, I 4.0. No way, upgrade, upgrade level 1 try, success...
Finally done, but so 1 to, and must ask the customer to upgrade.
Now that you can use the group_concat function, what about the other functions?
Try it with count function 1. Success. I... I want to cry, after all this time... It's that simple...
Now release 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 item is superfluous. Just ignore it.
Alas, it turns out that mysql is so stupid that he was cheated by a single blow. I am the only one who is depressed. I hope you won't be troubled by this problem.
Oh, yes, and by the way, group by must be placed before order by and limit, otherwise there will be an error.