mysql - 更新具有特定条件和计数器的表中的列(意外结果)

Question

我想知道这个条件的查询是什么？我想用计数器更新列，但我的计数器必须重置分组依据。让我用一个例子来解释一下:

   Data set:
  +----+------------+----------+------+-------+
  | id | content_id | title_id | path | order |
  +----+------------+----------+------+-------+
  |  1 |          1 |       20 | NJ   |     0 |
  |  2 |          1 |       20 | CA   |     0 |
  |  3 |          1 |       20 | LA   |     0 |
  |  4 |          1 |       20 | MM   |     0 |
  |  5 |          1 |       30 | AX   |     0 |
  |  6 |          1 |       30 | ER   |     0 |
  |  7 |          2 |       10 | NJ   |     0 |
  |  8 |          2 |       10 | CA   |     0 |
  |  9 |          2 |       20 | AX   |     0 |
  | 10 |          2 |       20 | CA   |     0 |
  | 11 |          2 |       30 | FF   |     0 |
  | 12 |          2 |       30 | EE   |     0 |
  +----+------------+----------+------+-------+

  Desired result set:
  +----+------------+----------+------+-------+
  | id | content_id | title_id | path | order |
  +----+------------+----------+------+-------+
  |  1 |          1 |       20 | NJ   |     1 |
  |  2 |          1 |       20 | CA   |     1 |
  |  3 |          1 |       20 | LA   |     1 |
  |  4 |          1 |       20 | MM   |     1 |
  |  5 |          1 |       30 | AX   |     2 |
  |  6 |          1 |       30 | ER   |     2 |
  |  7 |          2 |       10 | NJ   |     1 |
  |  8 |          2 |       10 | CA   |     1 |
  |  9 |          2 |       20 | AX   |     2 |
  | 10 |          2 |       20 | CA   |     2 |
  | 11 |          2 |       30 | FF   |     3 |
  | 12 |          2 |       30 | EE   |     3 |
  +----+------------+----------+------+-------+

我使用下面的查询，但是当“内容”更改时“订单”无法重置。

set @counter = 0;
update paper as t, (select (@counter := @counter+1) as cou, t2.title_id
from paper as t2 group by t2.title_id) as t3 
set t.order = t3.cou where t.title_id = t3.title_id;

我也使用下面的查询但没有成功:

set @counter = 0;
update paper as t, (select (@counter := @counter+1) as cou, t2.title_id 
from paper as t2 group by t2.title_id, t2.content_id) as t3
set t.order = t3.cou where t.title_id = t3.title_id;

编辑:我将表架构添加到 sqlfiddle。 Link (谢谢@Payam)

Answer 1

这是一种方法，但可能还有更优雅的解决方案......

 UPDATE paper a
   JOIN 
      ( 

        SELECT p.id 
             , p.content_id 
             , p.title_id 
             , p.path 
             , IF(@prev_content = content_id,
                           IF(@prev_title = title_id,@i:=@i,@i:=@i+1)
                  ,@i:=1) new_order
             , @prev_title := title_id
             , @prev_content := content_id 
          FROM paper p
             , (SELECT @i:=0,@prev_content:=NULL,@prev_title:=NULL) vars 
         ORDER 
            BY id

      ) b
     ON b.id = a.id
    SET a.order = b.new_order;