mysql - 如何为特定项目创建计数列

Question

我有以下架构(mysql)

create table test(
  userid int(11) not null,
  item varchar(15),
  bookid int(11));

insert into test values ('1','journal',NULL);
insert into test values ('1','journal',NULL);
insert into test values ('1','book',NULL);
insert into test values ('2','book',NULL);
insert into test values ('2','journal',NULL);
insert into test values ('1','book',NULL);
insert into test values ('2','journal',NULL);
insert into test values ('3','book',NULL);
insert into test values ('1','book',NULL);
insert into test values ('1','journal',NULL);
insert into test values ('3','journal',NULL);
insert into test values ('1','journal',NULL);
insert into test values ('2','journal',NULL);
insert into test values ('2','book',NULL);
insert into test values ('2','journal',NULL);
insert into test values ('1','journal',NULL);
insert into test values ('3','book',NULL);
insert into test values ('3','book',NULL);
insert into test values ('3','book',NULL);
insert into test values ('3','book',NULL);

每当有一本书时，我都会尝试在 bookid 列中分配一个以 1 开头的自动增量。对于每个用户，编号再次从 1 开始。我知道可以通过创建单独的表来完成此操作。有没有办法可以避免这种情况，并在这个表中使用某种更新查询并更新列 bookid 来实现这一点？我正在尝试获得类似于以下内容的输出:

userid,item,bookid
'1','journal',NULL
'1','journal',NULL
'1','book',1
'2','book',1
'2','journal',NULL
'1','book',2
'2','journal',NULL
'3','book',1
'1','book',3
'1','journal',NULL
'3','journal',NULL
'1','journal',NULL
'2','journal',NULL
'2','book',2
'2','journal',NULL
'1','journal',NULL
'3','book',2
'3','book',3
'3','book',4
'3','book',5

如果有人可以指导我如何实现这一目标，我将不胜感激？

Answer 1

这是一个想法...

  drop table if exists test;

  create table test
  (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,userid int not null
  ,item varchar(15) NOT NULL
  );

  insert into test (userid,item) values 
   (1,'journal')
  ,(1,'journal')
  ,(1,'book')
  ,(2,'book')
  ,(2,'journal')
  ,(1,'book')
  ,(2,'journal')
  ,(3,'book')
  ,(1,'book')
  ,(1,'journal')
  ,(3,'journal')
  ,(1,'journal')
  ,(2,'journal')
  ,(2,'book')
  ,(2,'journal')
  ,(1,'journal')
  ,(3,'book')
  ,(3,'book')
  ,(3,'book')
  ,(3,'book');

  SELECT x.*
       , COUNT(*) rank 
    FROM test x 
    JOIN test y 
      ON y.userid = x.userid 
     AND y.item = x.item 
     AND y.id <= x.id 
   GROUP 
      BY id 
   ORDER 
      BY userid
       , item
       , rank;
  +----+--------+---------+------+
  | id | userid | item    | rank |
  +----+--------+---------+------+
  |  3 |      1 | book    |    1 |
  |  6 |      1 | book    |    2 |
  |  9 |      1 | book    |    3 |
  |  1 |      1 | journal |    1 |
  |  2 |      1 | journal |    2 |
  | 10 |      1 | journal |    3 |
  | 12 |      1 | journal |    4 |
  | 16 |      1 | journal |    5 |
  |  4 |      2 | book    |    1 |
  | 14 |      2 | book    |    2 |
  |  5 |      2 | journal |    1 |
  |  7 |      2 | journal |    2 |
  | 13 |      2 | journal |    3 |
  | 15 |      2 | journal |    4 |
  |  8 |      3 | book    |    1 |
  | 17 |      3 | book    |    2 |
  | 18 |      3 | book    |    3 |
  | 19 |      3 | book    |    4 |
  | 20 |      3 | book    |    5 |
  | 11 |      3 | journal |    1 |
  +----+--------+---------+------+

请注意，MyISAM 实际上允许您使用复合 PK，其中该复合的一部分是自动递增 id，但 InnoDB 禁止这样做。

在较大的数据集上，沿着这些思路进行查询可能会更加有效......

SELECT id
     , userid
     , item
     , IF(@userid=userid,IF(@item=item,@i:=@i+1,@i:=1),@i:=1) rank
     , @userid := userid
     , @item := item 
  FROM test
     , (SELECT @userid = NULL,@item:='',@i:=1) vars
 ORDER 
    BY userid,item,id;