MySQL简单的select语句，如果行值与前一个值不同则创建自动增量

Question

到目前为止我有以下查询:

SET @cnt = 0;
SELECT (
  @cnt := @cnt +1
) AS rowNumber, brand
FROM  `cars` 

它输出以下内容:

rowNumber | brand
1         | fiat
2         | fiat
3         | fiat
4         | bmw
5         | bmw
6         | mercedes
7         | mercedes
8         | tesla
9         | tesla

但是我想实现:

rowNumber | brand
1         | fiat
1         | fiat
1         | fiat
2         | bmw
2         | bmw
3         | mercedes
3         | mercedes
4         | tesla
4         | tesla

我怎样才能实现这个目标？

Answer 1

您可以使用其他变量来做到这一点:

select (@rn := if(@b = brand, @rn,
                  if(@b := brand, @rn + 1, @rn + 1)
                 )
       ) as rowNumber, brand
from `cars` c cross join
     (select @b := '', @rn := 0) params
order by brand;