Some comparisons and usages of distinct and group by statements in Mysql _mysql

The bibliography used in the user verification is recorded in the datasheet, and now I want to take out all the bibliographies, using distinct and group by to get the results I want, but I find that the return results are arranged in a different order, and the distinct will be displayed in the sequence of data, and group By Will do a sort (typically ASC).

DISTINCT is actually very similar to the implementation of a group by operation, except that only one record is taken out of each group by after the groups by. Therefore, the implementation of the DISTINCT and GROUP by implementation is almost the same, there is not much difference, the same can be done through a loose index scan or a compact index scan.

which distinct and group by which efficiency is higher?

The distinct operation only needs to find out all the different values. The group by operation also prepares for other aggregate functions. From this point on, the GROUP by operation should do more work than distinct.

But in fact, GROUP by efficiency will be higher, why? For the distinct operation, it reads all the records, and group by needs to read as many records as there are groups, which means a lot less than the actual number of records.

Here's a look at some of the usage shares of distinct and group by in MySQL.

CREATE TABLE ' student ' (          
      ' name ' varchar ' not NULL DEFAULT ', 
      ' age ' int (a) default ' 0 '        
     ) engine=innodb DEF Ault 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

Select distinct name,age from student;

Return

A  5
c  0

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

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

SELECT * from student;

Name Age height
C  2  123
C  2  456
b  222

Group by two columns at the same time

Select Name,age,sum (height) from student group by Name,age;

b  222
C  2  579

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

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

Copy Code code as follows:

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 10;

Unknown  8738
together through the  1432
Wind continue to blow  1432  the
Ghost 1432 No
sleep  1432
Robergi Super Hi Party Continuous suite  780 refused  to
play  1432
again  1432  18
every day love You more  1480
thousand words  1794  18

Copy Code code as follows:

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
together through  1432
Wind continue to blow  1432 The
Ghost  1432
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.

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.

Copy Code code as follows:

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

Copy Code code as follows:

Select Feedback_id,songid,songname,max (singername), Max (time) as New_time from feedback Group by SongID ORDER by New_time D Esc

Copy Code code as follows:

MySQL Query error:select COUNT (*) from feedback GROUP by SongID order by New_time DESC Error info:unknown column ' New_tim E ' in ' order clause '