php - Mysql创建一行名为Rank

Question

让我用我的问题告诉你一个例子。
例如，我们有一个名为 (order) 的表，它将所有订单和购买插入到该表中。

表 A(订单):

+--------------------------+
| CustomerKey | ProductKey |
+--------------------------+
| 306545      | pro1       |
| 597864      | pro3       |
| 784678      | pro2       |
| 905479      | pro3       |
| 306545      | pro1       |
| 348965      | pro3       |
| 784678      | pro3       |
+--------------------------+

现在我想订购并获得我们的畅销产品

查询输出:

+-------------------------------+
| id | ProductKey | numberSold  |
+-------------------------------+
| 1  | pro3       | 4           |
| 2  | pro1       | 2           |
| 3  | pro2       | 1           |
+-------------------------------+     

我写了我查询这个:

select ProductKey, count(1) as numberSold from A group by ProductKey order by count(1) desc

但还不完整!此查询需要一个名为 rank 的行(查看下面的查询输出):

+-------------------------------------+
| id | ProductKey | numberSold | rank |
+-------------------------------------|
| 1  | pro3       | 4          | 1    |
| 2  | pro1       | 2          | 2    |
| 3  | pro2       | 1          | 3    |
+------------------------------+------+

Answer 1

可能这就是您正在寻找的:

SET @rank=0;
SELECT 
    ProductKey, 
    count(1) AS numberSold, 
    @rank:=@rank+1 AS rank 
  FROM A 
  GROUP BY ProductKey ORDER BY numberSold DESC