mysql - Mysql 中的 RowNum，分组依据（MySQL 5.x）

Question

我想在 MySql 中做一个 rownum，但使用 group by。所以当de column city改变时rownum需要重新开始。

我试过了：

> select city, category_name, sales,    @row_number:=CASE WHEN
> @db_names=sub_table.city THEN @row_number+1 ELSE 1 END AS row_number
> from ( SELECT city, category_name, sum(subtotal) as sales from
> customers     join orders on customers.customer_id = orders.customer_id
>     join order_items on orders.order_id = order_items.order_id
>     join products on order_items.product_id = products.product_id
>     join categories on products.category_id = categories.category_id
>     group by city, category_name
>     ) sub_table ORDER BY sub_table.city ASC, sub_table.sales DESC;

但我得到了这个结果：

标签： mysql

Answer 1

您非常接近，只是变量@db_name 未赋值：

select city, category_name, sales,    
  @row_number:=CASE WHEN
    @db_names=sub_table.city THEN @row_number+1 ELSE 1 END AS row_number,
  @db_name:=sub_table.city
from ( SELECT city, category_name, sum(subtotal) as sales from
customers     join orders on customers.customer_id = orders.customer_id
    join order_items on orders.order_id = order_items.order_id
    join products on order_items.product_id = products.product_id
    join categories on products.category_id = categories.category_id
    group by city, category_name
) sub_table ORDER BY sub_table.city ASC, sub_table.sales DESC;