T-SQL создает иерархию из упорядоченных чисел
У меня есть такая таблица:
Id code
1 10
2 11
3 20
4 21
5 30
6 31
7 32
8 40
9 10
10 11
11 20
12 21
13 30
14 31
15 32
16 40
17 20
18 21
19 30
20 31
21 32
22 40
23 20
24 21
25 30
26 31
27 32
28 40
29 20
30 21
31 30
32 31
33 32
34 40
35 20
36 21
37 30
38 31
39 32
40 40
41 41
42 90
Столбец id представляет просто порядок записей.
Столбец code представляет тип записи.
Проблема в том, что записи являются частью иерархии, как показано здесь:
Мне нужно получить родителя каждой записи:
Id code Parent
1 10 1
2 11 1
3 20 1
4 21 3
5 30 3
6 31 3
7 32 3
8 40 3
9 10 9
10 11 9
11 20 9
12 21 11
13 30 11
14 31 11
15 32 11
16 40 11
17 20 9
18 21 17
19 30 17
20 31 17
21 32 17
22 40 17
23 20 9
24 21 23
25 30 23
26 31 23
27 32 23
28 40 23
29 20 9
30 21 29
31 30 29
32 31 29
33 32 29
34 40 29
35 20 9
36 21 35
37 30 35
38 31 35
39 32 35
40 40 35
41 41 40
42 90 42
Родитель каждой записи должен быть выражен как ее Id.
Правила такие:
- Десятки - их собственные родители, поскольку они являются корнями
- 90-е - их собственные родители, так как они - конец данных
- 20-й родитель - это предыдущие 10
- 21 30 31 32 33 родитель — предыдущий 20
- 40 и 50 родителей это предыдущие 20
- 41 родитель - предыдущий 40
Как видите, порядок записей очень важен.
Я пытался решить это декларативно (с помощью lag() и т. д.) и императивно с помощью циклов, но не смог найти решения.
Пожалуйста помоги
Легко решается с lag() с ignore nulls который не поддерживает SQL Server, но можно подражать.
@GSerg, нет, это не так, потому что у него неправильные числа в поле parentId.
Ответы 2
я думаю, вы должны добавить FOREIGN KEYparentId ссылку Id в существующую таблицу, заполнить этот новый столбец UPDATE или получить данные для его заполнения из внешнего источника, а затем вы должны сделать SELECT * FROM tableName ORDER BY parentId, чтобы получить древовидную структуру
Это должно работать. Вероятно, это не оптимальная производительность, но довольно ясно, что она делает, поэтому ее должно быть легко изменить, если (когда!) изменится ваша иерархия.
Очевидно, что он может давать нули, если ваша иерархия или порядок не соответствуют тому, что вы указали.
CREATE TABLE #data(id INT, code INT);
INSERT INTO #data values
(1 , 10),(2 , 11),(3 , 20),(4 , 21),(5 , 30),(6 , 31),(7 , 32),(8 , 40),(9 , 10),(10 , 11),
(11 , 20),(12 , 21),(13 , 30),(14 , 31),(15 , 32),(16 , 40),(17 , 20),(18 , 21),(19 , 30),(20 , 31),
(21 , 32),(22 , 40),(23 , 20),(24 , 21),(25 , 30),(26 , 31),(27 , 32),(28 , 40),(29 , 20),(30 , 21),
(31 , 30),(32 , 31),(33 , 32),(34 , 40),(35 , 20),(36 , 21),(37 , 30),(38 , 31),(39 , 32),(40 , 40),
(41 , 41),(42 , 90);
WITH
tens AS (SELECT id FROM #data WHERE code = 10),
twenties AS (SELECT id FROM #data WHERE code = 20),
forties AS (SELECT id FROM #data WHERE code = 40)
SELECT #data.id,
#data.code,
CASE WHEN code IN (10,90) THEN #data.id
WHEN code IN (11,20) THEN prev_ten.id
WHEN code IN (21,30,31,32,33,40,50) THEN prev_twenty.id
WHEN code = 41 THEN prev_forty.id
ELSE NULL
END AS Parent
FROM #data
OUTER APPLY (SELECT TOP (1) id FROM tens WHERE tens.id < #data.id ORDER BY tens.id DESC) AS prev_ten
OUTER APPLY (SELECT TOP (1) id FROM twenties WHERE twenties.id < #data.id ORDER BY twenties.id DESC) AS prev_twenty
OUTER APPLY (SELECT TOP (1) id FROM forties WHERE forties.id < #data.id ORDER BY forties.id DESC) AS prev_forty;
Вы так запутали проблему, что за ней очень трудно уследить. Является ли первый набор данных фактической таблицей, а второй набор данных — желаемым результатом? А я вообще не понимаю ваших правил по сравнению с вашим вторым набором данных.