mysql, ASC 使用现有数据的一系列数字

2022-01-05 00:00:00 mysql phpmyadmin

美好的一天，我有这个查询，它按数字大的顺序排列我的数据

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

这会给我带来这个结果

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

新列，rank，它是一个固定的 1,2,3，不会随着用户名和描述名称的变化而变化.

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

推荐答案

我知道在使用带有 group by 的变量时有时会出现问题.我不确定这是否会影响计数变量，但它肯定会影响运行总和.更安全的方法是使用子查询:

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).

相关文章