mysql如何在select中生成行索引(rank)？

rhfm7lfc 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(413)

通过mysql查询，我得到了以下表：

orders | customer |
1      | A        |
1      | A        |
1      | B        |
1      | B        |
1      | B        |

仅使用mysql，我需要为每个客户创建一个索引顺序为ocurrence的列，以获得以下表：

orders | customer | index
1      | A        | 1
1      | A        | 2
1      | B        | 1
1      | B        | 2
1      | B        | 3

我试着用这个：

set @i=1;
while @i<99999 do
  select 
    count(order_id) as 'orders',
    customer_id as 'customer',
    @i as 'index'
  from
    orders
  set @i= @i+1;     
end while;

但我的陈述有错误。对不起，我不知道怎么做了。任何想法都将不胜感激。

mysql

来源：https://stackoverflow.com/questions/54046541/mysql-how-to-generate-row-index-rank-in-select

2条答案

按热度按时间

lnlaulya1#

SELECT 
    orders.* , 
    customerOrderCount , 
    IF(@i > 1, @i:= @i - 1, @i:=customerOrdercount) AS orderIndex
FROM (SELECT * FROM orders ORDER BY customer ASC ) AS orders
    JOIN (SELECT customer, count(*) AS customerOrderCount FROM orders GROUP BY 
customer) counts USING (customer)
ORDER BY customer ASC, orderIndex;

赞(0）回复(0）举报 2021-06-16

bq9c1y662#

mysql 8.0的标准方法是使用窗口功能：

SELECT orders, customer, 
  ROW_NUMBER() OVER (PARTITION BY customer) AS `index`
FROM orders

在MySQL8.0之前，您可以使用内联用户变量进行操作。

SET @i = 0, @c = '';

SELECT 
  orders, 
  IF(@c=customer, @i:=@i+1, @i:=1) AS `index`, 
  @c:=customer AS customer
FROM orders
ORDER BY customer;

不幸的是，这需要 customer 列位于 index 列。

赞(0）回复(0）举报 2021-06-16

我来回答

mysql如何在select中生成行索引(rank)？

2条答案

相关问题

热门标签

最新问答