mysql基于先前记录的增量值 [英] mysql increment value based on previous record

问题描述

我有一张桌子

Id|Parent|Counter
--|------|-------
 1| A    | NULL
 2| A    | NULL 
 3| A    | NULL
 4| B    | NULL
 5| B    | NULL
 6| C    | NULL
 7| D    | NULL
 8| D    | NULL

我想更新表，以使计数器列只要以前的parent = parent为更新(+1).如果没有，则counter =1如此:

I want to update the table such that the counter column is update (+1) as long as previous parent = parent. If not, counter =1 so:

Id|Parent|Counter
--|------|-------
 1| A    | 1
 2| A    | 2
 3| A    | 3
 4| B    | 1
 5| B    | 2
 6| C    | 1
 7| D    | 1
 8| D    | 2

我大约有350万条记录.

I have about 3.5M records.

我可以选择查询，但可以使其与更新配合使用.这就是我所拥有的:

I can get a select query but can get it to work with update. This is what I have:

SELECT  t.Parent, (
  SELECT COUNT( * ) 
  FROM bomitems AS x
  WHERE x.id <= t.id
  AND x.Parent = t.Parent
  ) AS Counter
FROM bomitems AS t

推荐答案

您编写了一个很好而聪明的查询来生成这些行号，该行号避免了使用会话变量的混乱解决方案.要进行更新，只需将表bomitems加入此查询:

You wrote a good and clever query to generate those row numbers, one which avoids a messy solution using session variables. To do the update, just join your table bomitems to this query:

UPDATE bomitems t1
INNER JOIN
(
    SELECT
    t.ID,
    t.Parent,
    (SELECT COUNT(*) FROM bomitems AS x WHERE x.id <= t.id AND x.Parent = t.Parent) AS Counter
    FROM bomitems t
) t2
    ON t1.ID = t2.ID
SET t1.Counter = t2.Counter;

我在本地MySQL Workbench上测试了此查询，它似乎正在工作.

I tested this query on my local MySQL Workbench and it appears to be working.

这篇关于mysql基于先前记录的增量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！