calculate rank of the user

问题:

I want to calculate rank using MySQL. I have a table called result

result_id    test_id    exam_id    user_id    percentage
   1            5          6          50          57
   2            5          6          58          76 
   3            5          6          65          42

I want to calculate the rank of the user according to his user_id and test_id like user_id(58) has 1 rank user_id(50) has 2 and so on

I tried query like

select percentage  from result where test_id=$test_id(i.e 5) and user_id=$user_id(i.e 58)

but it gives 76 and doesn't give the rank

I also tried

select percentage from result where test_id=$test_id(i.e 5) 

but it gives me 57,76,42

Is there any way by which I can calculate the rank of the user?

回答1:

You can simply use ORDER BY percentage DESC. Try this...

$con = mysqli_connect("host","user","password","db_name");
if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = 'SELECT * FROM result ORDER BY percentage DESC';
$result = mysqli_query( $con, $sql );
if (!mysqli_query($con, $sql)) {
    die('Error: '. mysqli_error($con));
}
$i = 1;
while($row = mysqli_fetch_array($result)) {
    echo 'User ID ' . $row['user_id'] . ' has rank  ' . $i . ' and percentage ' . $row['percentage'] . '</br>';
    $i++;
}
mysql_close($con);

回答2:

You can do this in MySQL without returning all the results. For a single user:

select percentage
       (select count(*)
        from result r2
        where test_id = r.test_id and r2.percentage > r.percentage
       ) + 1 as rank
from result r
where test_id=$test_id(i.e 5) and user_id=$user_id(i.e 58

回答3:

You can use variables to calculate a DenseRank for a table, I have assumed this ranking would re-commence at 1 for each combination of test_id and exam_id which I treat as the "partition" fields, and for this query the "value" we will use to determine rank is percentage (in descending order); so the highest percentage gets a rank of 1

like this:

set @patn = '';
set @rank = 1;
set @valu = '';
set @rept = 1;

SELECT    test_id
        , exam_id
        , user_id
        , percentage
        , DenseRank
FROM (
      SELECT  test_id
            , exam_id
            , user_id
            , percentage
            , @rank := if(@patn=grp, if(@valu=percentage, @rank, @rank + @rept),1) as DenseRank
            , @rept := if(@patn=grp, if(@valu=percentage, @rept, 1),1)
            , @patn := grp
            , @valu := percentage     
      FROM  (
             select
                      concat(test_id,exam_id) AS grp
                    , test_id
                    , exam_id
                    , user_id
                    , percentage
              from results 
              order by
                      test_id
                    , exam_id
                    , percentage DESC
            ) dta
     ) dtb;

• @patn tracks changes to a "partition"
• @rept tracks changes in the "value" column
• @rept does not increment if "value" repeats
• @rank is the calculated dense rank

The inner query (dta) must be sorted by:

• the "partition" columns e.g. test_id, exam_id
• and the "value" column e.g. percentage

Next level outer query (dtb),

• compare current row "partition" with previous row "partition"
• if the "partition" changes, then rank = 1
• else compare the previous row "value" with current row "value"
• if the "value" changes, increment the rank by 1
• else assign the same rank
• the current "partition" is stored ; to be considered the previous "partition" on the next row
• the current "value" is stored; to be considered the previous "value" on the next row

Lastly:

• the required columns are chosen

Extending the sample data a little this is a result:

| TEST_ID | EXAM_ID | USER_ID | PERCENTAGE | DENSERANK |
|---------|---------|---------|------------|-----------|
|       5 |       6 |     580 |         76 |         1 |
|       5 |       6 |      50 |         57 |         2 |
|       5 |       6 |     581 |         57 |         2 |
|       5 |       6 |     582 |         57 |         2 |
|       5 |       6 |     583 |         42 |         3 |
|       5 |       6 |      65 |         42 |         3 |

|       6 |       6 |     580 |         76 |         1 |

Where the ranking calculation should only step up by 1 (hence the term "dense") and it recommences at one for the next "partition".

nb: the term partition alludes to partition by used in dbms system that do have analytic functions for ranking (Oracle, DB2, SQL Server, PostGres, others)

See this SQLfiddle demo