The rank function of PHP and MySQL imitating Excel

PHP like the rank function of Excel is also a reference to the code on the Internet, but there is no two-dimensional array in the case of rank ranking, so their code slightly changed a bit, you can run the experiment directly.

<?php$arr = array (          array (' s ' = ' 99 ', ' R ' = ' 1 ', ' a ' = = ' a '),           array (' s ' = ' ~ ', ' r ' = > ' 1 ', ' B ' = ' B '),           array (' s ' = ' + ', ' r ' = ') 2 ', ' c ' = ' C '),           array (' s ' = ' = ' 101 ', ' r ' = ' 3 ', ' d ' = = ' d ')         );echo  ' <pre> ';p rint_r (rank ($arr, ' s ', ' R ') )///Get a set number of the rank of the array Function rank (array  $array, $s, $r) {         foreach ($array  as  $k = $v) {           $marr []  =  $v [$s];        }         foreach ($array  as  $val) {          $ Repeat=get_array_repeats ($vAl[$s], $marr);           $num =gt_array_values ($val [$s], $marr) ;           $rank [$r]=count ($marr)-$num-$repeat +1;            $rank 2[] = array_merge ($val, $rank);         }        return  $rank 2;} Get a number smaller than yourself function gt_array_values ($val,array  $array) {          $num =0;        for ($i =0; $i <count ($array); $i + +) {                 if ($val > $array [$i]) {                           $num ++;                 }         }        return $ Num;} Get this number of repetitions function get_array_repeats ($string,array  $array)  {          $count  = array_count_values ($array);         foreach  ($count  as  $key  =>  $value)  {                  if  ($key  ==  $string)  {                   return  $value;                   }         }}

Logic is also very simple, the position of the students, equal to the total number of people minus their grades lower than the number of people, and then minus the same number as their own results, plus one.

Lower than their own number of people, and with their own results as much as the number of people, respectively, is two independent functions, the former needs to cycle all student performance, the number of students than their own low-grade cumulative, each student to carry out this cycle, a little consumption of performance ha. The latter simply solves the problem by simply referencing the array function.

This can be based on the test results (field name s), the candidate's ranking (field name R), but it is clear that the number of cycles a bit more, it is difficult to imagine when the amount of data is very large when the situation is, so it is recommended to query the time directly execute the SQL statement to achieve the Rank function function, this online there are many Like how the rank function in MySQL is implemented

In this example, when we are looking for the rank value of the trainee with ID 1, run the SQL statement as follows:

Select Tmp.id,tmp.name,tmp.score,@j:[email protected]+1 as j,@k:= (case if @pre_score =tmp.score then @k else @j end) as R  Ank, @pre_score: =tmp.score as Pre_scorefrom (SELECT * from score where ID =1) as TMP, (select @k: =0,@j:=0, @pre_score: =0) as Mscore

J is sequence number, K is rank value

@k:= (case if @pre_score =tmp.score then @k else @j end

This means that the order number is used only when the value is different between the previous and the two orders, which is the key place.

Finally run SQL, with the GetRow function to get the ID 1 of this data, get rank value.

This article is from the "Flying Potato God teach" blog, please be sure to keep this source http://1105190775.blog.51cto.com/10048144/1891460

The rank function of PHP and MySQL imitating Excel