mysql - 如何在mysql表中重复序列号

Question

我有以下 mysql 表

 name   city
umar     mzd
ali      mzd
john     bagh
saeed    bagh
irum     bagh

我想添加序列号，当城市名称更改时序列号会重复，如下

S.No   name     city
  1    umar      mzd
  2    ali       mzd
  1   john      bagh
  2   saeed     bagh
  3   irum      bagh

Answer 1

在这里，尝试一下:

select name, city, serial_number from
    (select name, city
    , (case when @city <> city then @citynum := 1 else @citynum := @citynum + 1 end) as serial_number
    , (case when @city <> city then @city := city end) as v
    from tbl_cities, (select @citynum := 0, @city := '') as c
order by city) as cities

这里是运行示例的链接:http://sqlfiddle.com/#!2/615f5/1 .

您需要将“tbl_cities”更改为表的名称。

我无法将其保存为 View 或在更新语句中使用，因为它使用变量。一种快速方法是使用上面的 select 语句创建一个新表，并在添加新数据时定期运行。

drop table if exists tbl_cities_serial;

create table tbl_cities_serial(select name, city, serial_number from
(select name, city
    , (case when @city <> city then @citynum := 1 else @citynum := @citynum + 1 end) as serial_number
    , (case when @city <> city then @city := city else @city end) as v
from tbl_cities, (select @citynum := 0, @city := '') as c
order by city) as cities)