When using mysql, and sometimes need to check out a field does not duplicate records, although there are distinct mysql provides the keyword to filter out unwanted duplicate records to retain only one, but often do not use it to return to the Number of duplicate records instead of using it to return all the values are not re-record. The reason is distinct objectives can only be returned to its field, but can not return to other fields, this problem plagued me long, can not be solved with a distinct, I only use two cyclic query to solve, and this amount of data for a very large station is undoubtedly a direct impact on efficiency.
Let's look at the following example:
Library structure about this, this is a simple example, the situation will be much more complex.
I would like to use a statement such as query by name does not duplicate all the data, it must use distinct remove unnecessary duplication of records.
select distinct name from table
The result is:
Seems to achieve the desired effect, and yet, I want to get the id value of it? Query to change about it:
select distinct name, id from table
Result will be:
how distinct not working? Role is played, but the role he had two fields, that is, have to have the same id and name will be excluded. . . . . . .
We changed to change the query:
select id, distinct name from table
Unfortunately, in addition to the error message you get nothing, distinct to be placed at the beginning. Difficult to place where conditions can not be distinct in? Energy, still error. . . . . . .
A lot of trouble, right? Indeed, effort failed to solve the problem.
Finally found a use mysql manual with group_concat (distinct name) with the group by name I need to achieve the functionality.
Be investigated carefully, group_concat support function is 4.1, halo, I 4.0. No way, upgrade or complete class a try, success. . . . . .
Finally buttoned up, but this way, they must require customers to upgrade the.
Sudden brainwave flash, since you can use group_concat function, that other functions work?
Quickly with a try count function successfully, the original's that simple. . . . . .
Will now release a full statement:
select *, count (distinct name) from table group by name
id name count (distinct name)
1 a 1
2 b 1
3 c 1
The last one is unnecessary, do not control on the line, aim to achieve. . . . .
Alas, the original mysql so stupid, what took him lightly deceived later, also depressed me, and now out hope that we will not be tossing this issue.
Oh, right, and then by the way, group by order by and limit must be placed before, or will be error.