mysql, ASC a series of number using existed data

问题:

Good day, I have this query which arrange my data in order of higher number

SELECT username,count(*) as description
FROM products
WHERE
description LIKE '%Yes%'

or

description LIKE '%yes%'
GROUP BY username
ORDER BY description ASC

which will bring me this outcome

username  |   description
   a      |     3
   b      |     1

I'm able to do the above, question is what should I do in order to make it like this?

 rank   |   username    |  description
  1     |     a         |    3
  2     |     b         |    1

new column, rank, which is a fixed 1,2,3 that won't change while the username and description name change.

回答1:

I know there are sometimes issues when using variables with group by. I'm not sure if this affects counting variables, but it definitely affects running sums. A safer approach is to use a subquery:

SELECT (@rn := @rn + 1) as rank, username, numdescription
FROM (SELECT p.username, count(*) as numdescription
      FROM products p
      WHERE description LIKE '%Yes%' or description LIKE '%yes%'
      GROUP BY p.username
     ) p CROSS JOIN
     (SELECT @rn := 0) vars
ORDER BY numdescription DESC;

Note that I renamed the output field. Using the same name seems confusing, because one is a description (string) and the other is a count (number).

回答2:

You should use a variable with initial value equal to zero and incremented by 1 for each row. Try the following query

SELECT  @n:=@n+1 as rank,username,count(*) as description
FROM products,(SELECT @n:= 0) AS num
WHERE
description LIKE '%Yes%'
or
description LIKE '%yes%'
GROUP BY username
ORDER BY description ASC

回答3:

add count while extracting the data from mysql fetch to the array in which you are storing

for example

$count = 1;

$product = array(); 
while ($row = mysql_fetch_array($q))
{ 

$count[]['rank'] = $count;
$product[]["username"] = $row["username"];
$product[]["description"] = $row["description"];
$count++;
}