Comparison and usage of distinct and groupby statements in MySQL _ MySQL

This article mainly introduces the comparison and usage of distinct and groupby in MySQL. There are many similarities between the two in query operations, if you need a friend, you can refer to the bibliography recorded in the data table for user verification. now, if you want to retrieve all the bibliography, you can use DISTINCT and group by to get the expected result, however, I found that the returned results are arranged differently. distinct is displayed in the order of data storage, while group by is sorted (generally ASC ).

DISTINCT is actually very similar to the implementation of the group by operation, except that only one record is retrieved in each GROUP after group. Therefore, the implementation of DISTINCT is similar to that of group by, and there is no big difference. It can also be achieved through loose index scanning or compact index scanning.

Which of the following is more efficient for DISTINCT and group?

The DISTINCT operation only needs to find all the different values. The group by operation also requires preparation for other aggregate functions. At this point, the group by Operation should do more work than DISTINCT.

But in fact, group by is more efficient. why? For the DISTINCT operation, it reads all records, and the number of records to be read by group by is as large as the number of groups in the GROUP, that is, it is much less than the actual number of records.

The following describes how to use distinct and group by in MySQL.

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  5a  5c  0

Use distinct to filter out records with the same columns

select distinct name,age from student;

Return

a  5c  0

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

select * from student;

c  2c  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  2c  5

3. test 3

select * from student;

name age heightc  2  123c  2  456b  20  222

Group by groups by two columns at the same time

select name,age,sum(height) from student group by name,age;

b  20  222c  2  579

Group by groups two columns at the same time, and adds the having condition to the end.

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 share over 1432 24 wind continues to blow 1432 23 ghost 1432 23 unintentional sleep 1432 23 Luo Baiji super hi party continuous concert 780 19 refuse to play 1432 19 wind then start again 1432 18 love you some more 1480 every day 18 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 spent 1432 24 wind continues 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.

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;

The code is as follows:

MySQL Query Error: SELECT COUNT(*) FROM feedback GROUP BY songid ORDER BY new_time DESC Error Info:Unknown column 'new_time' in 'order clause'

The above are some of the comparison and usage of distinct and group by statements in MySQL _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!