mysql - MySQL 5.0 根据不同的列分配编号

Question

我一直在试图弄清楚如何分配数字（在我的情况下，组号基于不同列中的值）。我有一个带有数字的表格，并根据该数字尝试分配组号。数字是表格的顺序，可以多行相同。

create table test (
    code varchar(10) primary key,
    num varchar(10) not null,
    name varchar(10) not null,
    surname varchar(10) not null);

insert into test values (1,9,'Tom', 'Smith');
insert into test values (2,9,'Adam','Blake');
insert into test values (3,15,'John','Smith');
insert into test values (4,15,'Adam','XYZ');
insert into test values (5,43,'John','Abc');
insert into test values (6,99,'Adam','Abc');
insert into test values (7,99,'John','Abc');

所以测试表是这样的：

并且所需的输出看起来像这样，其中 grp 值始终是从 1 开始的连续数字。

结果代码：

create table result (
    code varchar(10) primary key,
    num varchar(10) not null,
    name varchar(10) not null,
    surname varchar(10) not null,
grp varchar(10) not null);

insert into result values (1,9,'Tom', 'Smith',1);
insert into result values (2,9,'Adam','Blake',1);
insert into result values (3,15,'John','Smith',2);
insert into result values (4,15,'Adam','XYZ',2);
insert into result values (5,43,'John','Abc',3);
insert into result values (6,99,'Adam','Abc',4);
insert into result values (7,99,'John','Abc',4);

这可以在不创建任何函数和变量的情况下实现吗？是否有任何描述它并且可以使用的伪列？

标签： mysqlsql

Answer 1

您可以使用相关子查询：

select t.*,
       (select count(distinct t2.num)
        from test t2
        where t2.num <= t.num
       ) as grp
from test t;

更有效的方法使用变量：

select t.*,
       (@grp := if(@n = t.num, @grp,
                   if(@n := t.num, @grp + 1, @grp + 1)
                  )
       ) as grp
from (select t.*
      from test t
      order by t.num
     ) t cross join
     (select @grp := 0, @n := -1) params;