Mysql中的RowNum，带有group by (MySQL 5.x)

Question

我想在MySql中做一个rownum，但使用的是group by。因此，当de column城市发生变化时，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;

但我得到的结果是：

​

​

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;

Answer 2

我编写了它，就像我在SO上进行的其他查询一样。

您还应该对表使用别名。

 SELECT 
    `city`,
    `category_name`,
    `sales`
     ,IF(@db_names = city , @row_number:= @row_number + 1  , @row_number:= 1 ) AS row1_number
     ,@db_names := 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
    ORDER BY city , category_name) sub_table
    ,(SELECT @dab_names := '') c1,(SELECT @row_number := 0) d1
ORDER BY sub_table.city ASC , sub_table.sales DESC;

如果没有一个样本来正确地测试它，那么