MySQL finds the rank in the current list based on the int value of the specified field

Look at the table structure and data first:

DROP TABLEIFEXISTS ' Ndb_record '; CREATE TABLE ' Ndb_record ' (' id ' bigint () notNULLAuto_increment COMMENT ' Measurement Records ',' user_id ' bigint () notNULLCOMMENT ' User ID ',' yellow ' int (11)DEFAULT NULLCOMMENT ' yellow state lasts longer ',' green ' int (11)DEFAULT NULLCOMMENT ' green state lasts longer ',' Blue ' int (11)DEFAULT NULLCOMMENT ' Blue state duration ',' Create_time 'Date DEFAULT NULLCOMMENT ' measuring time ',' Week ' varchar (20)DEFAULT NULLCOMMENT ' weeks ',PRIMARYKEY(' id ')) ENGINE=innodb auto_increment=35DEFAULTcharset=UTF8;-- ------------------------------Records of Ndb_record-- ----------------------------INSERT into ' Ndb_record ' VALUES (' 17 ', ' 13 ', ' 8 ', ' 7 ', ' 6 ', ' 2017-03-23 ', ' Thursday 'INSERT into ' Ndb_record ' VALUES (' 18 ', ' 13 ', ' 8 ', ' 7 ', ' 6 ', ' 2017-03-22 ', ' Wednesday 'INSERT into ' Ndb_record ' VALUES (' 19 ', ' 13 ', ' 8 ', ' 7 ', ' 6 ', ' 2017-03-20 ', ' Monday 'INSERT into ' Ndb_record ' VALUES (' 20 ', ' 13 ', ' 8 ', ' 7 ', ' 6 ', ' 2017-03-19 ', ' Sunday 'INSERT into ' Ndb_record ' VALUES (' 21 ', ' 13 ', ' 8 ', ' 7 ', ' 6 ', ' 2017-03-18 ', ' Saturday 'INSERT into ' Ndb_record ' VALUES (' 22 ', ' 13 ', ' 8 ', ' 7 ', ' 8 ', ' 2017-03-23 ', ' Thursday 'INSERT into ' Ndb_record ' VALUES (' 23 ', ' 13 ', ' 8 ', ' 7 ', ' 1 ', ' 2017-03-20 ', ' Monday 'INSERT into ' Ndb_record ' VALUES (' 24 ', ' 13 ', ' 8 ', ' 7 ', ' 2 ', ' 2017-03-14 ', ' Tuesday 'INSERT into ' Ndb_record ' VALUES (' 25 ', ' 13 ', ' 8 ', ' 7 ', ' 3 ', ' 2017-03-17 ', ' Friday 'INSERT into ' Ndb_record ' VALUES (' 26 ', ' 13 ', ' 8 ', ' 7 ', ' 4 ', ' 2017-03-16 ', ' Thursday 'INSERT into ' Ndb_record ' VALUES (' 27 ', ' 12 ', ' 8 ', ' 7 ', ' 4 ', ' 2017-03-21 ', ' Tuesday 'INSERT into ' Ndb_record ' VALUES (' 28 ', ' 12 ', ' 8 ', ' 7 ', ' 4 ', ' 2017-03-20 ', ' Monday 'INSERT into ' Ndb_record ' VALUES (' 29 ', ' 12 ', ' 8 ', ' 7 ', ' 4 ', ' 2017-03-20 ', ' Monday 'INSERT into ' Ndb_record ' VALUES (' 30 ', ' 12 ', ' 6 ', ' 7 ', ' 4 ', ' 2017-03-19 ', ' Sunday 'INSERT into ' Ndb_record ' VALUES (' 31 ', ' 12 ', ' 6 ', ' 7 ', ' 3 ', ' 2017-03-18 ', ' Saturday 'INSERT into ' Ndb_record ' VALUES (' 32 ', ' 16 ', ' 6 ', ' 7 ', ' 3 ', ' 2017-03-16 ', ' Thursday 'INSERT into ' Ndb_record ' VALUES (' 33 ', ' 16 ', ' 6 ', ' 7 ', ' 3 ', ' 2017-03-31 ', ' Friday 'INSERT into ' Ndb_record ' VALUES (' 34 ', ' 16 ', ' 6 ', ' 6 ', ' 0 ', ' 2017-04-05 ', ' Wednesday ');

The problem she gives is that this SQL statement counts the sum of each field and then finds the rank of the sum of the times associated with the specified user_id.

 as times from Ndb_record GROUP by user_id;

The result of the query is:

I've given two ways to do a SQL implementation.

First Kind

Select O_d from (select A.*,        as  o_d from  (select user_idas times from  Ndb_record GROUP by user_id ORDER by Times DESC) a,        : = 0) r) b WHERE user_id =13

The second Kind

SELECTCount(*) asO_dfrom (SELECT user_id,(SUM (yellow)+ SUM (green) +SUM (blue)) asTimes from Ndb_record GROUP by user_id) Awhere times>=(select times from (select user_id,(SUM (yellow)+ SUM (green) +SUM (blue)) asTimes from Ndb_record GROUP by user_id ) b WHERE ' user_id '= 13    )

The query results are the same as the first.

You may not understand the above, and then I'll take a simple table example:

CREATE TABLE ' test ' (  ' id ' int (NULL auto_increment COMMENT ' primary key ',  ' name ' varchar ( NULL DEFAULT ' COMMENT ' name ',  ' age ' int (NULLDEFAULT ' 0 ' COMMENT ' ages ',  KEY  (' id ')) ENGINEDEFAULT Charset=utf8

Table created, look at the results. Select * FROM Test:

For example, we want to check is Harry in these five people of the age row, visual Zhao Liu is the eldest, that is, ranked first, Liu Seven old five, ranked fifth.

Upper statement:

SELECT * FROM (select t.*,        as  o_d  * from Test ORDER BY age desc) T,        
    := 0) R) b where ID =1

The condition of the query is Id=1, which is Zhang San, the result is 4.

Count  as O_d from (select-from-Test) a Where age >= (select-from- test) b where ' id ' = ' 1 ');

Complete.

Although the results come out, but also please the predecessors of a lot of advice where the shortage! Salute! ~

MySQL finds the rank in the current list based on the int value of the specified field