MySQL method from: http://www.cnblogs.com/jjcc/p/5896588.html
# # #在网上看到一篇, it's a great way
For example, to get the top 3 of the class, MySQL can be implemented with Group_concat + GROUP by + substring_index .
Test form
DROP TABLE IF EXISTS ' test ';
CREATE TABLE ' Test ' (
' id ' int (one) DEFAULT NULL,
' Name ' varchar (DEFAULT NULL),
' Score ' int (one) DEFAULT NULL,
' Class ' char (+) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8;
Inserting data
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 1 ', ' bobdd ', ' 25 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 2 ', ' xx ', ' 20 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 3 ', ' Jack ', ' 30 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 4 ', ' Bill ', ' 32 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 5 ', ' Nick ', ' 22 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 6 ', ' Kathy ', ' 18 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 7 ', ' Steve ', ' 36 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 8 ', ' Anne ', ' 25 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' 9 ', ' Kathy ', ' 18 ', ' 2 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' One ', ' Bob1 ', ' 25 ', ' 3 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' n ', ' Jane1 ', ' 20 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Jack1 ', ' 30 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Bill1 ', ' 32 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' A ', ' Nick1 ', ' 22 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Kathy1 ', ' 18 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' n ', ' Steve1 ', ' 36 ', ' 4 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' + ', ' Anne1 ', ' 25 ', ' 1 ');
INSERT into ' test ' (' id ', ' name ', ' Score ', ' class ') VALUES (' n ', ' Kathy1 ', ' 18 ', ' 2 ');
Use Group_concat + GROUP by group to get top 3
Select Group_concat (t1.id) as IDs from (
SELECT T.class, Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3) as-ID from
Test T GROUP by T.class
) T1
Get
Note that t.id ORDER by t.score DESC scores from high to low.
The above statement simply gets the total ID. But the conversion to the column is not very good to get. You can split it up with union all.
Get first place
SELECT T.class, Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 1) as-ID from
Test T GROUP by T.class
UNION ALL
--second place
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 2), ', ', -1) as-ID from
Test T GROUP by T.class
UNION ALL
--Third place
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3), ', ', -1) as-ID from
Test T GROUP by T.class
Well, now that we've got a list,
Use in to complete the final step
SELECT class,score,name from test where ID in (
SELECT ID from
(SELECT T.class, Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 1) as-ID from
Test T GROUP by T.class
UNION ALL
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 2), ', ', -1) as-ID from
Test T GROUP by T.class
UNION ALL
SELECT T.class, Substring_index (Substring_index (Group_concat (t.id ORDER by t.score Desc), ', ', 3), ', ', -1) as-ID from
Test T GROUP by T.class) T2
) ORDER by Class Asc,score desc
Final result
If you simply use MySQL to write, too complex, it is recommended to use r:1, order to score in descending order 2,df[,head (. sd,3), By=class] #对班级分组, returns the first three rows of each group
MySQL group_concat+ GROUP by + Substring_index gets the first few