Distinct removal of duplicate values in MySQL

, and can not return to other fields, this problem let me worry for a long time, with the distinct can not be resolved, I only use the double loop query to solve, and so for a very large amount of data station, it will undoubtedly directly affect the efficiency. So I spent a lot of time studying this.

MySQL's distinct keyword has a lot to do with your unexpected usefulness.
1. Can be used when count does not duplicate a record
For example, select COUNT (DISTINCT ID) from TableName;
is to calculate the number of records with different IDs in the Talbebname table.

2, when you need to return a record of the specific values of different IDs can be used
For example, select DISTINCT ID from tablename;
Returns the specific values of different IDs in the Talbebname table

3. The above situation 2 is ambiguous when you need to return the results of more than 2 columns in the MySQL table.
For example, select DISTINCT ID, type from tablename;
Actually returns the result that ID and type are not the same at the same time, that is, distinct two fields at the same time, must have ID and Tyoe are the same to be excluded, and we expect the result is not the same

Cases

code is as follows	copy code
CREATE TABLE ' Student ' (                               ' name ' varchar () not NULL Default ',             ' age ' int (a) default ' 0 '                           ) Engine=innodb DEFAULT charset=latin1

1. Test A
SELECT * from student;
A 5
A 5
C 0

Use distinct to filter out records with the same two columns

The code is as follows	Copy Code
Select distinct name,age from student;

Return
A 5
C 0

2. Test Two
Change the table student data to read as follows:

The code is as follows	Copy Code
SELECT * from student; C 2 C 5 Select distinct name,age from student;

Returns the following, indicating that when there are more than one column of fields after distinct, only the values of each column are exactly the same to filter
C 2
C 5

3. Test Three

The code is as follows	Copy Code
SELECT * from student; Name Age Height

C 2 123
C 2 456
b 20 222

The code is as follows	Copy Code
Group by two columns at the same time Select Name,age,sum (height) from student group by Name,age; b 20 222 C 2 579

Group by two columns at the same time, followed by the having condition

The code is as follows	Copy Code
Select Name,age,sum (height) as n from student group by Name,age has n > 500;

Return
C 2 579

4. Test Four
About GROUP by back limit test

code is as follows	copy code
select Songname,sengerid,count (Sengerid) as n from T_song Group by Songname,sengerid has n > 1 order by n Desc,songid ASC l Imit 10;

Unknown     8738   
Cross     1432   
Wind continues to blow     1432   
Qian female ghosts     1432   
Unintentional sleep      1432   
Robergi Super Hi Party Suite     780   
refuse to play      1432   
Wind up     1432   
Love You more every day      1480   
thousand words     1794   

The code is as follows	Copy Code
Select Songname,sengerid,count (Sengerid) as n from T_song Group by Songname,sengerid has n > 1 order by N Desc,songi D ASC limit 5;

Unknown 8738 40
To spend 1432 24 together.
The wind continues to blow 1432 23
Qian Female Ghosts 1432 23
Unintentional sleep 1432 23

As you can see from the above two tests, if the SQL statement contains limit,limit is a limit operation that is grouped with group by and related calculations, instead of grouping the specified number of records after limit, You can see from the data in the column of n that the value of each row is greater than 10.

5. Test Five
In the following two forms of distinct can be the same number of records, the writing is not the same, the result is the same.

The code is as follows	Copy Code
Select COUNT (Distinct (songid)) from feedback; Select COUNT (Distinct songid) from feedback;

6. Test Six
Field Singername is String,max (singername), if singername some columns are empty and some are not empty, max (Singername) takes a non-empty value, if one is zxx, and one is a lady, then the zxx is taken, The letter was taken smoothly.

The code is as follows	Copy Code
Select Feedback_id,songid,songname,max (singername), Max (time) as New_time from feedback Group by SongID ORDER by New_time D Esc

Order of Where,group By,order by and limit in 7.SQL statements
where Xxx,group by Xxx,order by Xxx,limit xxx

8. Questions about GROUP BY and Count
If you have group by in an SQL statement, it is best not to convert count SQL to select COUNT (*) from XXX, or the field between the select and from is likely to be used later, for example

code is as follows	copy code
Select Feedback_id,songid,songname,max (Singername), Max (time) as New_time from feedback Group by SongID ORDER by New_time desc; MySQL Query error:select COUNT (*) from feedback GROUP by SongID order by New_time DESC Error info:unknown column ' n Ew_time ' in ' order clause '