#sql #database #tree
#sql #База данных #дерево
Вопрос:
таблица — пользователь
столбцы — (userId, name, ManagerID)
строки —
(1,nilesh,0)
(2,nikhil,1)
(3,nitin ,2)
(4,Ruchi,2)
если я дам идентификатор пользователя, он должен перечислить всех сообщающих ему людей.
если я дам userId = 2, он должен вернуть 3,4.
Правильный ли этот запрос
SELECT ad3.userId
FROM user au , user au2 , user au3
WHERE
ad.managerId = ad2.managerId AND
ad3.managerId = ad2.userId AND
ad.userId=2
Существует ли какой-либо эффективный способ управления древовидной структурой в БД?
Как насчет правого и левого конечных путей?
Комментарии:
1. Какую базу данных вы используете?
2. Если вы ищете альтернативные способы реализации иерархий в реляционной базе данных, вы можете взглянуть на эту презентацию. slideshare.net/billkarwin/models-for-hierarchical-data
3. Очень важно знать механизм базы данных. То, что вы хотите, это предложение «WITH», но оно не поддерживается повсеместно.
4. Предложение «WITH» называется «рекурсивным общим табличным выражением» и поддерживается PostgreSQL, Firebird, Oracle, DB2, SQL Server, Sybase и H2
Ответ №1:
Я использую текстовое поле для работы с деревьями в SQL. Это проще, чем использовать значения влево / вправо.
Давайте возьмем пример из статьи MySQL:
-----------------------
| name |
-----------------------
| ELECTRONICS |
| TELEVISIONS |
| TUBE |
| LCD |
| PLASMA |
| GAME CONSOLES |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
| CD PLAYERS |
| 2 WAY RADIOS |
| FRS |
-----------------------
В результате получилась бы таблица, подобная этой:
Id ParentId Lineage Name
1 null /1/ ELECTRONICS
2 1 /1/2/ TELEVISIONS
3 2 /1/2/3/ TUBE
4 2 /1/2/4/ LCD
5 2 /1/2/5/ PLASMA
6 6 /1/6/ GAME CONSOLES
7 1 /1/7/ PORTABLE ELECTRONICS
8 7 /1/7/8/ MP3 PLAYERS
9 8 /1/7/8/9/ FLASH
10 7 /1/7/10/ CD PLAYERS
11 1 /1/11/ 2 WAY RADIOS
12 11 /1/11/12/ FRS
Для поиска всех переносимых файлов вы просто используете Lineage из portables:
SELECT * FROM theTable WHERE Lineage LIKE '/1/7/%'
Минусы:
- Вам нужно выполнять ОБНОВЛЕНИЕ после каждой вставки, чтобы добавить PK к Lineage
Предложение:
Обычно я добавляю еще один столбец, в который я помещаю путь в виде текста (например 'electronics/televisions/tube'
)
Комментарии:
1. он имеет дело с подстановочными знаками в sql, которые не настолько надежны. не эффективны . В случае глубокого дерева это проблема 🙂 Поиск по строке с использованием подстановочных знаков всегда снижает производительность.
2. a) Ненадежно? Что именно ненадежно? б) Я не говорю, что это универсальное решение, работающее для всех сценариев. Насколько глубоким может быть ваше дерево сотрудников? Вряд ли это проблема. c) Мне трудно понять, как это может снизить производительность в вашем случае. Звучит как преждевременная оптимизация.
3. чувак, ты запускаешь запрос в SQL с подстановочным знаком и сравниваешь числа. Запрос для подстановочной карточки займет больше времени. Следовательно, больше времени поиска для всех дочерних элементов. В первый раз я использовал подход, подобный вашему, но он не настолько эффективен.
4. Я не говорю, что ваш подход неправильный, я говорю, что он не настолько эффективен.;)
5. потрясающее решение я знаю производительность, но для меня просто нет лучшего способа, поскольку я имею дело с существующей таблицей и работает очень быстро с тысячами строк и использую entity framwork
Ответ №2:
Что-то вроде этого (ANSI SQL):
WITH RECURSIVE emptree (userid, name, managerid) AS (
SELECT userid,
name,
managerid
FROM the_table
WHERE userid = 2
UNION ALL
SELECT c.userid,
c.name,
c.managerid
FROM the_table c
JOIN emptree p ON p.userid = c.managerid
)
SELECT *
FROM emptree
Комментарии:
1. Ответ Дэвида Стила более эффективен, чем ваш! Хорошая попытка 🙂
2. Но только если дерево меняется не очень часто.
3. значение a_horse_with_no_name верно, но если это действительно для менеджеров и персонала, то оно, скорее всего, будет меняться более одного раза в день.
4. @Дэвид Стил: абсолютно. Но всегда полезно знать все варианты 😉
5. вы правы. Эта модель хороша, когда происходит более частое извлечение узлов, чем обновление узла 🙂
Ответ №3:
На мой взгляд, проблема с моделью списка смежности заключается в том, что с ней становится трудно работать в SQL, особенно когда вы не знаете, насколько глубоко будет вложена ваша древовидная структура.
Упомянутый вами «путь влево и вправо», вероятно, является моделью вложенного набора и позволяет хранить подобные вещи
LFT RGT Name
1 8 nilesh
2 7 nikhil
3 4 nitin
5 6 Ruchi
Затем вы можете найти всех подчиненных, просто
SELECT Name FROM Hierarchy WHERE LFT BETWEEN @LFT AND @RGT
Я думаю, что с этим гораздо проще иметь дело при выполнении запросов, но сложнее сделать для модификаций дерева. Если ваши данные не сильно меняются, то я думаю, что это гораздо лучшее решение. (Хотя не все со мной согласятся)
Здесь есть очень хороший учебник
Комментарии:
1. С моделью смежности очень легко иметь дело, если СУБД поддерживает иерархические запросы (что в настоящее время делают почти все основные СУБД)
2. @ David Steele чувак, ты молодец! Я ожидал этого ответа 🙂
3. Хороший момент, мне действительно нужно их посмотреть. Однако я все еще думаю, что если данные не сильно меняются, часто лучше использовать NS, поскольку запросы для чтения данных намного проще писать и понимать.
4. Спасибо Nilesh. Рад, что вам это нравится.
5. В зависимости от используемой вами базы данных модальный список смежности может быть намного быстрее. Смотрите explainextended.com/2009/09/24 /… .
Ответ №4:
У меня есть простой ответ на этот вопрос:
Создание таблицы:
Create Table #AllChilds(id int)
Элемент списка:
Declare @ParentId int;
set @ParentId=(Select Id from Employee Where id=1)
-- Here put the id as the record for which you want all childs
While(@ParentId is not null )
begin
set @ParentId=(Select Id from Employee Where ParentId=@ParentId)
insert into #AllChilds values(@ParentId)
end
Смотрите результат:
Select Id from #AllChilds
Очистка:
Drop table #AllChilds
Ответ №5:
SELECT user.id FROM user WHERE user.managerid = 2
Это то, чего вы хотите?
Комментарии:
1. Не выглядит как древовидная структура.
2. нет, чувак, это древовидная структура, здесь я даю родительский идентификатор, тогда я должен получить идентификатор всех прямых или косвенных узлов.
3. если вы даете userid = 1, должен ли он возвращать 2, 3, 4 или только 2?
4. этот запрос идеально подходит для этой цели ))) если вам нужно только 2 из userid = 1 )))
5. если вам нужно построить дерево со всеми узлами и листьями, лучший способ — выбрать все строки, упорядочить их по parent_id и построить с помощью вашего языка программирования… но в любом случае, если таблица огромная, она не будет работать очень быстро, потому что невозможно спрятать слона в коробку со спичками )))