How to remove duplicate values using distinct in mysql

When using mysql, you sometimes need to query records with unique fields. Although mysql provides the keyword distinct to filter out redundant duplicate records, only one record is retained, but it is often used to return the number of records that do not repeat, instead of returning all values that do not record the record. The reason is that distinct can only return its target field

When using mysql, you sometimes need to query records with unique fields. Although mysql provides the keyword distinct to filter out redundant duplicate records, only one record is retained, but it is often used to return the number of records that do not repeat, instead of returning all values that do not record the record. The reason is that distinct can only return its target field

But other fields cannot be returned. This problem has plagued me for a long time. If distinct cannot be used to solve it, I only need to use dual-loop queries, this will undoubtedly directly affect the efficiency of a station with a large data volume. So I spent a lot of time studying this problem.

The DISTINCT keyword of mysql is of much unexpected use.
1. It can be used when count does not repeat records
For example, select count (DISTINCT id) FROM tablename;
Calculate the number of records with different IDs in the talbebname table.

2. You can use
For example, select distinct id FROM tablename;
Returns the specific values of different IDs in the talbebname table.

3. In case 2 above, there will be ambiguity when you need to return results with more than two columns in the mysql table
For example, select distinct id, type FROM tablename;
In fact, the returned result is a result of different IDs and types, that is, DISTINCT serves two fields at the same time. It must be the same id and tyoe to be excluded. It is different from the expected result.

Example

The Code is as follows:
Create table 'student '( 'Name' varchar (20) not null default '', 'Age' int (10) DEFAULT '0' ) ENGINE = InnoDB default charset = latin1

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

Use distinct to filter out records with the same columns

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

Return
A 5
C 0

2. Test 2
Change the data of student to the following:

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

The following is returned, indicating that when there are more than one column of fields after distinct, only the values of each column are identical.
C 2
C 5

3. Test 3

The Code is as follows:
Select * from student; Name age height

C 2 123
C 2 456
B 20 222

The Code is as follows:
Group by groups 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 groups two columns at the same time, and adds the having condition to the end.

The Code is as follows:
Select name, age, sum (height) as n from student group by name, age having n> 500;

Return
C 2 579

4. Test 4
Test the limit operation after group

The Code is as follows:
Select songname, sengerid, count (sengerid) as n from t_song group by songname, sengerid having n> 1 order by n DESC, songid ASC limit 10;

Unknown 8738 40
Spend 1432 together
Continue to blow 1432 23
Ghost 1432 23
Unintentional sleep 1432 23
Luo Baiji, super hi party, continuous group concert 780 19
Refuse to play 1432 again
1432 when the wind starts again
Love you more than 1480 every day
1794 million words 18

The Code is as follows:
Select songname, sengerid, count (sengerid) as n from t_song group by songname, sengerid having n> 1 order by n DESC, songid ASC limit 5;

Unknown 8738 40
Spend 1432 together
Continue to blow 1432 23
Ghost 1432 23
Unintentional sleep 1432 23

After the above two tests, we can see that if the SQL statement contains limit, limit groups the group by statement and performs the related calculated limit operations, instead of grouping the specified number of records after limit, we can see that the value of each row of data in the n column is greater than 10.

5. Test 5
In the following two forms of distinct, the same number of records can be obtained. The results are the same as the number of records written differently.

The Code is as follows:
Select count (distinct (songid) from feedback; Select count (distinct songid) from feedback;

6. Test 6
Field singername is string, max (singername). If some singername columns are empty and some are not empty, max (singername) takes a non-empty value. If one column is zxx, if the column is lady, zxx is used and the letter is followed.

The Code is as follows:
Select feedback_id, songid, songname, max (singername), max (time) as new_time from feedback group by songid order by new_time desc;

7. SQL statement where, group by, order by, and limit order
Where xxx, group by xxx, order by xxx, limit xxx

8. Questions about group by and count
If the SQL statement contains group by, it is better not to convert count SQL to select count (*) from xxx. Otherwise, the fields between select and from may be used later. For example:

The Code is as follows:
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 'new _ time' in 'order clause'