Resolve Mysql not repeating field value summation _mysql

Source: Internet
Author: User
Tags mysql manual
When using MySQL, there are times when you need to query for records that are not duplicated in a field, although MySQL provides a distinct keyword to filter out excess duplicates, but it often only uses it to return the number of records that are not duplicates, rather than using it to return all values that are not logged. The reason for this is that distinct can only return its target field. And can not return to other fields, this problem let me worry for a long time, with distinct can not be resolved, I only use the double loop query to solve, and so for a very large amount of data station, will undoubtedly directly affect the efficiency. So I spent a lot of time studying the problem and I couldn't find the solution online.
Let's take a look at the following examples:
Table
ID Name
1 A
2 b
3 C
4 C
5 b
The library structure is probably like this, this is just a simple example, the actual situation will be much more complicated.
For example, I want to use a statement query to get the name does not duplicate all the data, it must use distinct to remove unnecessary duplicate records.
Select DISTINCT name from table
the results obtained are:
Name
A

C
It seems to be working, 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 play a role? The effect is up, but he also works on two fields, that is, the ID must be the same as name will be excluded ...
We will change the query statement:
Select ID, distinct name from table
Unfortunately, you can't get anything except the wrong message, distinct must be placed at the beginning. Is it hard to put distinct in the where? Can, still error ....
Isn't that a problem? Indeed, the effort has failed to solve the problem. No way, keep looking for someone to ask.
Pulling a Java programmer from the company, he showed me that Oracle was using distinct and didn't find a solution in MySQL, and he suggested that I try group by after work.
Try for a long time, also not, finally in the MySQL manual to find a usage, with GROUP_CONCAT (distinct name) with group by name to achieve the function I need, excited, God save me also, quickly try.
Error............ Depressed....... Even the MySQL manual with me, first gave me hope, and then pushed me to disappointment, good ruthless ....
Again careful check, Group_concat function is 4.1 support, Halo, I 4.0. No way, upgrade, finish level a try, success ...
It's finally done, but it's going to require a client upgrade.
A sudden flash of a mind, since the GROUP_CONCAT function can be used, that other functions can do?
Use the Count function to try, success, I ... Want to cry ah, so much time. It was so 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 one is superfluous, do not need to control on the line, to achieve ...
Alas, the original MySQL so stupid, gently put him fooled past, depressed also on me, now take out hope that we do not be this problem toss.
Oh, yes, and by the way, group by must be placed before the order by and limit, otherwise it will complain, almost, I continue to busy ...
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.