#sql-server
#sql-server
Вопрос:
У меня есть мои childID и ParentID в 2 столбцах, однако я хочу разделить эти столбцы на несколько столбцов, но все они по-прежнему указывают на своего родителя, и, если применимо, также указывают на родителя этого родителя.
Возможно, в наборе данных всего 3 уровня, как показано ниже, но в некоторых случаях может быть и 4 уровня, что усложняет задачу, поскольку она должна быть несколько динамичной.
Ниже приведен мой текущий набор данных, дочерняя / родительская таблица:
ID, PARID,
1,
1,
10, 1
11, 1
20, 2
21, 2
100, 10
101, 10
110, 11
111, 11
200, 20
201, 20
210, 21
211, 21
И как я хочу, чтобы это было похоже на следующее:
LVL1 LVL2 LVL3
1,
1,
1, 10,
1, 11,
2, 20,
2, 21,
1, 10, 100,
1, 10, 101,
1, 11, 110,
1, 11, 111,
2, 20, 200,
2, 20, 201,
2, 21, 210,
2, 21, 211,
Имена идентификаторов не обязательно так, как я хочу, чтобы они назывались, но это просто в качестве примера. Кроме того, не всегда дочерние элементы всегда включают идентификатор родителей.
Я не могу найти нужную информацию в Интернете, в итоге я только нахожу, как создать дочернюю / родительскую таблицу, вместо того, чтобы пытаться наоборот.
Любая помощь приветствуется.
Редактировать: Благодаря реакции пока что я показываю свои фактические данные, хотя на самом деле в них 12000 строк И, как вы можете видеть, у них есть автоматически сгенерированный уникальный ключ, который может потребоваться знать:
OBJECTID,GROUNDID,MAINGROUNDID,
1, 024,
2, 025,
3, 026,
4, 02610, 026,
5, 02620, 026,
6, 02630, 026,
7, 02640, 026,
8, 02650, 026,
9, 027,
10, 028,
11, 029,
12, 030,
13, 03010, 030,
14, 03020, 030,
15, 03030, 030,
16, 03040, 030,
17, 030401, 03040,
Редактировать, результаты пока, однако двойные строки:
GROUNDID,Lvl1,Lvl2,Lvl3,Lvl4
006 006 NULL NULL NULL
007 007 NULL NULL NULL
008 008 NULL NULL NULL
009 009 NULL NULL NULL
010 010 NULL NULL NULL
011 011 NULL NULL NULL
014 014 NULL NULL NULL
015 015 NULL NULL NULL
016 016 NULL NULL NULL
017 017 NULL NULL NULL
018 018 NULL NULL NULL
019 019 NULL NULL NULL
020 020 NULL NULL NULL
021 021 NULL NULL NULL
022 022 NULL NULL NULL
023 023 NULL NULL NULL
024 024 NULL NULL NULL
025 025 NULL NULL NULL
026 026 NULL NULL NULL
02610 026 02610 NULL NULL
02620 026 02620 NULL NULL
02630 026 02630 NULL NULL
02640 026 02640 NULL NULL
02650 026 02650 NULL NULL
02610 02610 NULL NULL NULL
02620 02620 NULL NULL NULL
02630 02630 NULL NULL NULL
02640 02640 NULL NULL NULL
02650 02650 NULL NULL NULL
С уважением,
Игорь
Ответ №1:
Вот вариант, использующий стандартный рекурсивный CTE для построения иерархии и немного XML для анализа столбцов
Пример
;with cteP as (
Select ID
,PARID
,PathID = cast(ID as varchar(max))
From YourTable
Where PARID is Null
Union All
Select ID = r.ID
,PARID = r.PARID
,PathID = p.PathID concat(',',r.ID)
From YourTable r
Join cteP p on r.PARID = p.ID)
Select A.ID
,B.*
From cteP A
Cross Apply (
Select Lvl1 = xDim.value('/x[1]','int')
,Lvl2 = xDim.value('/x[2]','int')
,Lvl3 = xDim.value('/x[3]','int')
,Lvl4 = xDim.value('/x[4]','int')
From ( values (cast('<x>' replace(PathID,',','</x><x>') '</x>' as xml))) B(xDim)
) B
Order By PathID
ВОЗВРАТ
РЕДАКТИРОВАТЬ — ОБНОВЛЕНО ДЛЯ РЕАЛЬНЫХ ДАННЫХ
;with cteP as (
Select GROUNDID
,MAINGROUNDID
,PathID = cast(GROUNDID as varchar(max))
From YourTable
Where MAINGROUNDID is Null
Union All
Select GROUNDID = r.GROUNDID
,MAINGROUNDID = r.MAINGROUNDID
,PathID = p.PathID concat(',',r.GROUNDID)
From YourTable r
Join cteP p on r.MAINGROUNDID = p.GROUNDID)
Select A.GROUNDID
,B.*
From cteP A
Cross Apply (
Select Lvl1 = xDim.value('/x[1]','varchar(50)')
,Lvl2 = xDim.value('/x[2]','varchar(50)')
,Lvl3 = xDim.value('/x[3]','varchar(50)')
,Lvl4 = xDim.value('/x[4]','varchar(50)')
From ( values (cast('<x>' replace(PathID,',','</x><x>') '</x>' as xml))) B(xDim)
) B
Order By PathID
ВОЗВРАТ
РЕДАКТИРОВАТЬ 2
Я предполагаю, что ваши исходные данные выглядят примерно так
Комментарии:
1. Спасибо за быстрый ответ! Я попробовал это и каким-то образом получил 0 строк. Я добавил к основному описанию, как выглядят мои реальные данные. Возможно, это будет более понятно.
2. @titatovenaar Смотрите РЕДАКТИРОВАНИЕ / обновление. Возможно, в следующий раз вы предоставите более репрезентативный образец.
3. Я согласен, и я думаю, что даже моя вторая попытка была недостаточно репрезентативной, поскольку я получаю эту ошибку: «Сообщение 240, уровень 16, состояние 1, типы строк 1 не совпадают между привязкой и рекурсивной частью в столбце «PathID» рекурсивного запроса «cteP».». Однако, когда я использую r.Ground также как varchar (max), он не выдает ошибку, но он снова показывает мне столбцы но с данными в 0 строк.
4. @titatovenaar Просто любопытно… имеет ли MAINGROUNDID верхнего уровня нулевые значения или пустые строки «?
5. MAINGROUNDID полностью пустой. Если я попытаюсь выбрать, где MAINGROUNDID равен NULL, я получу 0 результатов. Так что, я думаю, они могут быть просто пустыми
Ответ №2:
Для меня ваш дизайн неправильный. У вас есть Person сущность с отношением «многие ко многим» к самой себе:
— у одного пользователя может быть несколько родителей
— один человек может быть родителем 0 или более дочерних элементов.
В реляционной модели отношение «многие ко многим» представлено таблицей отношений. Следовательно, у вас должно быть 2 таблицы:
- Лица: идентификатор, имя, дата рождения,…
- Взаимосвязь: ParentID, childID
Это позволит вам представить ЛЮБУЮ глубину отношений родитель / потомок.
Комментарии:
1. Достаточно справедливо, но на самом деле, который я не добавлял, но может потребоваться: у всех есть уникальный автоматически сгенерированный ключ: 1,2,3,4 и т.д.
2. @titatovenaar Я не думаю, что это влияет на мой ответ или дизайн
3. Это означает, что я не совсем понимаю, о чем вы говорите. У меня есть таблица транзакций, по которой я хочу иметь возможность фильтровать с помощью таблицы измерений объекта, которую я показываю выше. Здесь я могу выбрать GROUNDID, но если под ним есть какой-либо дочерний элемент, он не показывает транзакции, которые на самом деле должны быть там. Я не понимаю, как помогают две разделенные таблицы, которые вы предпочитаете. Можете ли вы объяснить? (также: на самом деле у меня нет возможности изменять таблицы, это дизайн в системе, который мне, к сожалению, не разрешено изменять!)