Как найти родителя на основе уровня узла

Question

У меня есть таблица tbl_budget с идентификаторами столбцов, budget_code и уровня. Уровень рассказать иерархии текущего табличного представления

я хочу найти родителя каждого бюджетного кода на основе его уровня

этот запрос действительно находит родителей первого уровня в порядке, но не после этого

ВЫБЕРИТЕ tb1.id, (выберите buget_code из tbl_budget, где level = tb1.level-1 LIMIT 0,1) в качестве родителя из tbl_budget tb1

Answer 1

Если я правильно понимаю вашу иерархию, то вы можете использовать LIKE сравнивать budget_codes в предложении ON:

select c.*, p.id as parent_id, p.budget_code as parent_budget_code
from tbl_budget c
left join tbl_budget p 
  on  p.level = c.level - 1
  and c.budget_code like concat(p.budget_code, '%')
order by c.id

Результат:

| id  | budget_code | level | parent_id | parent_budget_code |
| --- | ----------- | ----- | --------- | ------------------ |
| 1   | 001         | 1     |           |                    |
| 2   | 001-1       | 2     | 1         | 001                |
| 3   | 001-2       | 2     | 1         | 001                |
| 4   | 001-2-1     | 3     | 3         | 001-2              |
| 5   | 002         | 1     |           |                    |
| 6   | 002-1       | 2     | 5         | 002                |
| 7   | 002-2       | 2     | 5         | 002                |

дб-скрипка

Другой способ заключается в использовании SUBSTRING_INDEX():

select c.*, p.id as parent_id, p.budget_code as parent_budget_code
from tbl_budget c
left join tbl_budget p 
  on p.budget_code = substring_index(c.budget_code, '-', c.level - 1)
order by c.id

Если вам нужно только budget_code родителя, то вам даже не нужно соединение, так как родительский код является частью дочернего кода, и вам нужно только извлечь его:

select c.*,
  substring_index(c.budget_code, '-', c.level - 1) as parent
from tbl_budget c

Answer 2

Оформить заказ на следующий запрос:

Схема (MySQL v5.7)

create table tbl_budget (
  `id` INT ,
  `budget_code` VARCHAR(255),
  `level` INT
);

INSERT INTO `tbl_budget` VALUES
(1, '001', 1),
(2, '001-1', 2),
(3, '001-2', 2),
(4, '001-2-1', 3),
(5, '002', 1),
(6, '002-1', 2),
(7, '002-2', 2);

---

запрос

SELECT 
  @count_dash := LENGTH(tb1.budget_code) - LENGTH(REPLACE(tb1.budget_code, '-', '')) as count_dash,  
  tb1.id, 
  tb1.budget_code, 
  SUBSTRING_INDEX(tb1.budget_code, '-', -1) as child_part,
  SUBSTRING_INDEX(tb1.budget_code, '-', @count_dash) as parent_part,
  (
    SELECT id 
    FROM tbl_budget 
    WHERE 
      `level` = tb1.level-1 AND parent_part = `budget_code`
    LIMIT 1
  ) as parent_id
FROM tbl_budget tb1;

| count_dash | child_part | parent_part | parent_id | id  | budget_code |
| ---------- | ---------- | ----------- | --------- | --- | ----------- |
| 0          | 001        |             |           | 1   | 001         |
| 1          | 1          | 001         | 1         | 2   | 001-1       |
| 1          | 2          | 001         | 1         | 3   | 001-2       |
| 2          | 1          | 001-2       | 3         | 4   | 001-2-1     |
| 0          | 002        |             |           | 5   | 002         |
| 1          | 1          | 002         | 5         | 6   | 002-1       |
| 1          | 2          | 002         | 5         | 7   | 002-2       |

Объяснение:

Я предполагаю, что в ваших данных есть последовательность именования. Проблема в том, что для получения родительского элемента записи вам не нужно только level но и часть кода для соответствия

Шаг 1:

Следующее подсчитывает число "-" в budget_code:

@count_dash := LENGTH(tb1.budget_code) - LENGTH(REPLACE(tb1.budget_code, '-', ''))

Шаг 2:

Теперь мы разделим budget_code на "-" и мы возвращаемся:

• последняя часть (-1) который является дочерним кодом
• все части, но не последняя, ​​которая является родительским кодом

Это делается с помощью SUBSTRING_INDEX, который разбивает заданную строку на этот символ и возвращает количество запрошенных частей

Шаг 3:

Поскольку у каждого кода есть родитель, мы присоединяемся и возвращаем идентификатор родителя. Чтобы найти правильный, нам нужно следующее:

1. Уровень родителей равен нашему уровню -1
2. Бюджетный код родителей равен нашей части родительского кода

Посмотреть на БД Fiddle

Способ 2

Выше показано, как получить родительский код (даже без объединения). Тем не менее, ваш запрос может быть упрощен до следующего:

SELECT 
  tb1.id, 
  tb1.budget_code, 
  (
    SELECT id
    FROM tbl_budget 
    WHERE 
      `level` = tb1.level-1 
       AND tb1.budget_code LIKE CONCAT(budget_code, '%')
    LIMIT 1
  ) as parent_id
FROM tbl_budget tb1;

В этом случае мы говорим: Найти запись, которая на один уровень выше текущей и чей бюджетный код включает текущую. Обратите внимание, что вы можете изменить parent_id на parent_code, однако, если требуется код, я бы избегал объединения