Simple analysis of MySQL GROUP by

1. Test data:

create table  ' T_class '   (' id '  bigint  (one), ' name '  varchar  (), ' age '   int  (one), ' class '  int  (one)); insert into  ' T_class '   (' id ',  ' name ',  ' Age ',  ' class ')  values (' 1 ', ' tom1 ', ' + ', ' 1 ');insert into  ' T_class '   (' id ',  ' name ',   ' Age ',  ' class ')  values (' 2 ', ' tom2 ', ' + ', ' 1 ');insert into  ' T_class '   (' id ',  ') Name ',  ' Age ',  ' class ')  values (' 4 ', ' Jerry2 ', ' 2 ');insert into  ' T_class '   (' id ') ,  ' name ',  ' Age ',  ' class ')  values (' 5 ', ' Hanchao ', ' One ', ' 3 ');insert into  ' T_class '   (' id ',  ' name ',  ' Age ',  ' class ')  values (' 6 ', ' Hanchao2 ', ' ' 3 '); Insert into   ' T_class '   (' id ',  ' name ',  ' Age ',  ' class ')  values (' 7 ', ' jerry3 ', ' + ', ' 2 '); insert  into  ' T_class '   (' id ',  ' name ',  ' Age ',  ' class ')  values (' 8 ', ' Jerry4 ', ' 23 ', ' 2 ') ); INSERT INTO  ' T_class '   (' id ',  ' name ',  ' Age ',  ' class ')  values (' 9 ', ' Jerry1 ', ' 33 ', ' 2 '); 

2. Requirements: Find information about the oldest students in each class.

Obviously, we're going to use group by,

eg

Select Id,name,max, class from T_class GROUP by class; "The wrong wording!! 】

However, this is the wrong wording!!

Of course, we can also write this:

SELECT * FROM (SELECT * to T_class ORDER by age DESC) as bgroup by class;

However, I do not recommend this because there are some unpredictable things to write about. We first order by and then group BY, the result may not be to take the oldest student information, specific reasons, I am still looking for, of course, you know the reason can also tell me, the evidence is as follows:

650) this.width=650; "src=" https://s5.51cto.com/wyfs02/M02/8C/E2/wKiom1h8eISx61U2AADIvLAXf4Q632.jpg "style=" float : none; "title=" test1.jpg "alt=" Wkiom1h8eisx61u2aadivlaxf4q632.jpg "/>

650) this.width=650; "src=" https://s5.51cto.com/wyfs02/M02/8C/DE/wKioL1h8eIXjW-mUAACyDDG__qk684.jpg "style=" float : none; "title=" test2.jpg "alt=" Wkiol1h8eixjw-muaacyddg__qk684.jpg "/>

So, we can write this:

/** Method 1:**/select * from T_class t where T.age = (SELECT MAX (age) from T_class WHERE t.class = Class) ORDER by class;/** Method 2 : **/select t_class.id,t_class.name,t_class.age,t_class.class from T_class INNER JOIN (SELECT Class,max (age) as MaxAge From T_class GROUP by Class) T on (T.class = T_class.class and t_class.age = T.maxage);

As for the efficiency of that method, you can test it yourself!

Can read this text, can better understand group by:http://www.cnblogs.com/wiseblog/articles/4475936.html

http://blog.csdn.net/john_hongming/article/details/42742965

Http://www.th7.cn/db/mysql/201502/91713.shtml

http://yueliangdao0608.blog.51cto.com/397025/81278

This article is from the "My Java World" blog, so be sure to keep this source http://hanchaohan.blog.51cto.com/2996417/1892304

Simple analysis of MySQL GROUP by