Написать сложно реализуемый алгоритм на Microsoft SQL Server

#sql_server

Здравствуйте.

Нужна помощь девелоперов с очень сильным алгоритмичным мышлением и опытом в SQL SERVER.

Имеем такую вот иерархию Account-ов 

, 

где Account_code - это, собственно, код аккаунта , Account_alias - название аккаунта,
Hierarchy_level - позиция в иерархии. Это не вся таблица, в ней также присутствуют
еще несколько Алиасов с уровнем в иерархии 0, 1 и т.д., всего больше 200 Алиасов с
уровнем иерархии от 0 и выше (ссылку на полную таблицу смотри ниже).

На даный момент эта иерархия находится в БД, в таблице ACCOUNT_HIERARCHY. Нам же
нужно переместить эту иерархию в другую таблицу, с такими колонками и содержимым:

ACC_CODE | ACC_ALIAS | HIER_LEVEL | PARENT_ID | CHILD_ID

CF-0000  |Cash Flow  |     0      |   NO P ID | CF - 0001

CF-0001  |Cash Fl Lev|     1      |   CF-0000 | CF - 0028

и т.д. При этом, у одного предка может быть несколько детей, и, что самое важное,
уровень иерархии может миновать ступень, как выделено на скриншоте (предок - 5 уровень,
дети - сразу 7)

Вот ссылка на Excel файл - https://www.dropbox.com/s/ym5apeueajozzqw/%D0%9A%D0%BD%D0%B8%D0%B3%D0%B0.xlsx?dl=0

Я пробовал написать что то спомощью курсора и фетчей, но выходит одна ахинея из за
отсутствия опыта. Буду безмерно благодарен за помощь.

UPDATE 1: Скрин с размещением таблицы в бд (лежит точно так же, как и в Excel файле)

UPDATE 2: В этой таблице лежит по сути 3 иерархии. Члены каждой иерархии идентифицируються
с помощью значение с Account_Code, каждое из которых начинается с CF / CE / SP.

То есть, СF - 0000 с уровнем иерархии 0 - это начало иерархии CF,

CE - 0000 с уровнем иерархии 0 - это начало иерархии CE,

SP - 0000 с уровнем иерархии 0 - это начало иерархии SP.


UPDATE 3: Быть может, выйдет использовать что то в этом духе (начал писать, но застрял)

DECLARE @Account_code varchar(50)
DECLARE @Account_alias varchar(255)
DECLARE @Hierarchy_level int

DECLARE @Parent_account_id varchar(50)
DECLARE @Child_account_id varchar(50)

DECLARE @Current_position varchar(50)

DECLARE c_account_hierarchy SCROLL CURSOR FOR
    SELECT ACCOUNT_CODE, ACCOUNT_NAME, HIERARCHY_LEVEL
        FROM RealEstate_RiskEngine_LDR.dbo.LDR_ACCOUNT_HIERARCHY

OPEN c_account_hierarchy

FETCH NEXT FROM c_account_hierarchy
INTO @Account_code, @Account_alias, @Hierarchy_level

WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @Hierarchy_level = 0 

            INSERT INTO RealEstate_RiskEngine_DB.dbo.ACCOUNT_HIERARCHY(...)
            VALUES (...)

    

Ответы

Ответ 1

Для начала надо обязательно добавить в таблицу колонку с возрастающей нумерацией
строк, без нее SQL не гарантирует порядка строк, без которого невозможно правильно
выстроить иерархию. Кроме того в запросе обязательно надо будет ссылаться на эти номера
строк. В связи с этим в свой пример я ввел такую колонку и назвал ее num.

Родительской записью является ближайшая запись, которая расположена до нее и у которой
уровень иерархии меньше. Т.е. берем максимальный номер записи с меньшей иерархией,
меньший текущего номера.

Потомком является запись, идущая непосредственно после данной, при условии, что её
уровень иерархии больше. Это условие можно проверить с помощью функции lead()

select a.*,
       max(b.num) parent_id,
       case when lead(a.Hierarchy_level) over(order by a.num) > a.Hierarchy_level
            then lead(a.num) over(order by a.num) end child_id
  from accounts a
  left join accounts b on b.num