mysql，ASC使用现有数据的一系列数字 [英] 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

这将带给我这个结果

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

新列，等级，它是固定的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).

这篇关于mysql，ASC使用现有数据的一系列数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！