mysql - MySQL查询维护自引用表的升序排列

我正在使用MySQL 5.6.17。

我有一个self-referencing表，让我们说TableA列为id (int), title (varchar(100)), parent_id(int), sort_order (int)。

parent_id列是引用同一表的foreign key的id。这样，我维护层次结构的N level。

表数据如下：

id    title         parent   sort_order
1     Item 1        NULL     1
2     Item 1.1      1        1
3     Item 1.2      1        4
4     Item 1.3      1        5
5     Item 2        NULL     3
6     Item 2.1      5        1
7     Item 2.1.1    6        4
8     Item 2.1.2    6        5
9     Item 2.2      5        3
10    Item 2.1.3    6        3

在这里，层次结构维护得很好，但排序顺序却不好。我想重新排序每个父项下的项。

结果数据应如下所示：

id    title         parent   sort_order
1     Item 1        NULL     1
2     Item 1.1      1        1
3     Item 1.2      1        2
4     Item 1.3      1        3
5     Item 2        NULL     2
6     Item 2.1      5        1
7     Item 2.1.1    6        1
8     Item 2.1.2    6        2
9     Item 2.2      5        2
10    Item 2.1.3    6        3

我试过下面显示的查询，以re-order父项下每个项目的排序顺序：

UPDATE TableA
CROSS JOIN (SELECT @rownumber := 0) r
SET TableA.sort_order = (@rownumber := @rownumber + 1)
WHERE TableA.parent IN (SELECT t.id FROM TableA t);

但它返回错误

Error Code: 1093
You can't specify target table 'TableA' for update in FROM clause

如果我从上述查询中删除了where子句，则它将按升序重设每个项目的排序顺序，但我希望父项下的每个项目都以排序顺序1开始。

知道如何实现吗？

提前致谢。

最佳答案

样本数据：

CREATE TABLE t
    (`id` int, `title` varchar(10), `parent` varchar(4), `sort_order` int)
;

INSERT INTO t
    (`id`, `title`, `parent`, `sort_order`)
VALUES
    (1, 'Item 1', NULL, 1),
    (2, 'Item 1.1', '1', 1),
    (3, 'Item 1.2', '1', 4),
    (4, 'Item 1.3', '1', 5),
    (5, 'Item 2', NULL, 3),
    (6, 'Item 2.1', '5', 1),
    (7, 'Item 2.1.1', '6', 4),
    (8, 'Item 2.1.2', '6', 5),
    (9, 'Item 2.3', '5', 3),
    (10, 'Item 2.1.3', '6', 3)
;

查询：

update t
join (
  select
  t.*,
  @so := if(coalesce(parent, '0') != @p, 1, @so + 1) as new_sort_order
  , @p := coalesce(parent, '0')
  from t,
  (select @so := 0, @p := null) var_init
  order by parent, id
  ) sq on t.id = sq.id
set t.sort_order = sq.new_sort_order;

结果：

select * from t;

| ID |      TITLE | PARENT | SORT_ORDER |
|----|------------|--------|------------|
|  1 |     Item 1 | (null) |          1 |
|  2 |   Item 1.1 |      1 |          1 |
|  3 |   Item 1.2 |      1 |          2 |
|  4 |   Item 1.3 |      1 |          3 |
|  5 |     Item 2 | (null) |          2 |
|  6 |   Item 2.1 |      5 |          1 |
|  7 | Item 2.1.1 |      6 |          1 |
|  8 | Item 2.1.2 |      6 |          2 |
|  9 |   Item 2.3 |      5 |          2 |
| 10 | Item 2.1.3 |      6 |          3 |

请记住，您必须在确定sort_order的顺序中指定一列。在这种情况下，我假设它是id。


看到它在sqlfiddle中实时运行