Строки заказа, которые заменяют идентификатор

Question

Допустим, у меня есть таблица с текущим идентификатором.

CREATE TABLE F
(
    id int,
    s char
)

INSERT INTO F VALUES(10)

и другая таблица со строками, которые могут либо заменить идентификатор, либо обновить что-либо.

CREATE TABLE M
(
    id int,
    old_id int,
    new_id int,
    type char,
    s char,
    d date
)

Где в типе указывает, заменяет ли он идентификатор или обновляет s.

INSERT INTO dbo.M (id, old_id, new_id, type, d, s) 
VALUES
(2, 20,NULL,'U', '2018-01-01', 'a'),
(3, 10,20,  'R', '2018-01-01', NULL),
(4, 10,NULL,'U', '2018-01-01', 'b'),
(5, 20,NULL,'U', '2018-01-01', 'c'),
(6, 30,NULL,'U', '2018-01-01', 'd'),
(7, 20,30,  'R', '2018-01-01', NULL),
(8, 30,NULL,'U', '2018-01-01', 'e'),
(9, 40,NULL,'U', '2018-01-01', 'f')

Если я хочу отсортировать строки так, чтобы на первом месте стояло "U", которые соответствуют идентификатору в F, затем "R", которые соответствуют этому идентификатору, тогда следующие строки должны сортироваться по идентификатору, который установлен каждым "R".

Таким образом, порядок для этого будет 4, 3, 2, 5, 7, 8 или 4, 3, 5, 2, 7, 8

Answer 1

Для этого вам понадобится рекурсия:

CREATE TABLE f(id int, s char);
INSERT INTO f(id) VALUES
(10);

CREATE TABLE m(id int, old_id int, new_id int, type char, s char, d date);
INSERT INTO m (id, old_id, new_id, type, d, s) VALUES
(2, 20, NULL, 'U', '2018-01-01', 'a'),
(3, 10, 20,   'R', '2018-01-01', NULL),
(4, 10, NULL, 'U', '2018-01-01', 'b'),
(5, 20, NULL, 'U', '2018-01-01', 'c'),
(6, 30, NULL, 'U', '2018-01-01', 'd'),
(7, 20, 30,   'R', '2018-01-01', NULL),
(8, 30, NULL, 'U', '2018-01-01', 'e'),
(9, 40, NULL, 'U', '2018-01-01', 'f');

WITH cte AS (
    SELECT *, 1 AS lvl
    FROM m AS base
    WHERE old_id = (SELECT id FROM f)
    UNION ALL
    SELECT curr.*, lvl + 1
    FROM m AS curr
    INNER JOIN cte AS prev ON prev.type = 'R' AND curr.old_id = prev.new_id
)
SELECT *
FROM cte
ORDER BY lvl, CASE type WHEN 'U' THEN 1 WHEN 'R' THEN 2 END

Результат:

| id | old_id | new_id | type | s | d          | lvl |
|----|--------|--------|------|---|------------|-----|
| 4  | 10     |        | U    | b | 01/01/2018 | 1   |
| 3  | 10     | 20     | R    |   | 01/01/2018 | 1   |
| 2  | 20     |        | U    | a | 01/01/2018 | 2   |
| 5  | 20     |        | U    | c | 01/01/2018 | 2   |
| 7  | 20     | 30     | R    |   | 01/01/2018 | 2   |
| 6  | 30     |        | U    | d | 01/01/2018 | 3   |
| 8  | 30     |        | U    | e | 01/01/2018 | 3   |

• Базовая часть рекурсивного cte содержит все строки, содержащие 10
• Затем вы продолжите, выбрав R строки из предыдущих итераций, чтобы получить новый идентификатор и выбрать строки из основной таблицы, где старый идентификатор = новый идентификатор
• Наконец, вы сортируете строки в порядке их обработки в cte.