SQL — есть ли рекомендуемый подход при присоединении к дереву на произвольном уровне?
Спасибо за прочтение. Я уверен, что есть разумный способ сделать то, что я пытаюсь сделать, но отсутствие опыта работы с БД подводит меня.
Я собираюсь значительно упростить, чтобы уточнить мой вопрос ….. У меня есть 3 таблицы
- таблица с именем сотрудников (employee_id, name, parent_id и т. д.).
- таблица, называемая проектами (project_id, project_name и т. д.)
- таблица, связывающая два, называемых назначенных_проекта (id, employee_id, project_id)
Таблица Employee имеет глубину до 6 уровней (хотя в некоторых местах может быть не более 3 уровней) и содержит около 900 сотрудников. например
A
/ \
B C
/|\
D E F
Теперь проекты можно назначать на любом уровне. Если я назначу его сотруднику А, то он должен быть связан также со всеми его подчиненными (B-> F). Если я назначу его C, то он должен быть связан с D + E + F, но я также хотел бы видеть, что он находится в пределах компетенции A.
Когда я поднимаю профиль сотрудника, я использую CTE, привязанный к соответствующему сотруднику, для рекурсивного поиска всех подчиненных. У меня есть проблема, для каждого из этих подчиненных, как мне присоединиться к таблице assign_projects, чтобы увидеть, какие проекты находятся в сфере компетенции этого человека?
Варианты, которые я рассматривал:
- сначала выполните часть CTE, затем в php, итерируйте по всем employee_ids для выполнения отдельных запросов, которые извлекают project_ids из таблицы назначенных_проектов
- при первоначальном назначении проекта сотруднику выполните итерацию по дереву и добавьте запись в assign_projects для каждого соответствующего сотрудника. Проблема здесь в том, что если сотрудники перемещаются по дереву (меняют работу), я хочу, чтобы они соответственно наследовали / удаляли проекты.
Чтобы было ясно, при создании профиля C я хотел бы видеть все проекты, назначенные A, C, D, E, F. При поднятии профиля B, я хотел бы видеть все проекты, связанные с A или B.
Кажется, должен быть способ сделать это в SQL, используя некоторую хитрость, для которой у меня просто нет опыта? Или я должен изменить структуру моего стола … Я тоже открыт для этого.
Надеюсь, я объяснил это правильно. Спасибо за любую помощь, ценим ваше время.
Решение
Я думаю, что вы, возможно, создали мир боли для себя. С этим древовидным представлением у вас есть возможность использовать рекурсию, чтобы достичь своей цели. Производительность, вероятно, будет отстойной, а изменения будут сложными. Я предлагаю вам прочитать http://ebooks.cawok.pro/Morgan.Kaufmann.Joe.Celkos.Trees.and.Hierarchies.in.SQL.for.Smarties.May.2004.eBook-DDU.pdf для лучшей альтернативы.
Это не без его головных болей. Вы действительно нуждаетесь в хранимых процедурах для операций INSERT и DELETE, но тип операции, о которой вы говорите, гораздо проще, если у вас есть настроенное дерево модели вложенного набора. Чтобы найти нужные данные, вы просто присоедините свою древовидную таблицу к таблице проектов и запросите все проекты, в которых назначенные сотрудники имеют левое и правое значения между левым и правым значениями менеджера. Чтобы назначить сотрудникам с помощью их менеджера, вы ищете узел с MAX (левое значение) < левое значение сотрудника и МИН (правое значение)> правое значение сотрудника.
IF OBJECT_ID('tempdb..#tree') IS NOT NULL
DROP TABLE #tree
create table #tree (emp varchar(10), lval int, rval int)
INSERT INTO #tree
SELECT 'Bob',1,12
UNION
SELECT 'Harry',2,5
UNION
SELECT 'Barry',6,11
UNION
SELECT 'Dave',3,4
UNION
SELECT 'Mavis',7,8
UNION
SELECT 'Maud',9,10
IF OBJECT_ID('tempdb..#Projects') IS NOT NULL
DROP TABLE #Projects
CREATE TABLE #Projects(projectid INT,projectname VARCHAR(20))
INSERT INTO #Projects
SELECT 1,'Project Mammoth'
UNION
SELECT 2,'Project Minnow'
UNION
SELECT 3,'Project medium'
IF OBJECT_ID('tempdb..#ProjectReleations') IS NOT NULL
DROP TABLE #ProjectRelations
CREATE TABLE #ProjectRelations(emp VARCHAR(10),projectid INT)
INSERT INTO #ProjectRelations
SELECT 'Barry',1
UNION
SELECT 'Maud',2
UNION
SELECT 'Dave',3--Find Mavis' immediate manager (Barry)
SELECT
*
FROM
#tree
LEFT JOIN
#ProjectRelations ON
#tree.emp=#Projectrelations.emp
LEFT JOIN
#Projects ON
#Projectrelations.projectid=#Projects.projectid
WHERE
#tree.lval=(
SELECT
MAX(tree.lval)
FROM
#tree
inner join
#tree as tree ON
tree.lval < #tree.lval
AND
tree.rval > #tree.rval
WHERE
#tree.emp='Mavis'
)
--Find all projects under a particular employee (including projects assigned to that employee). If you want only projects assigned to subordinates use #tree.lval > rather than #tree.lval >=
SELECT
*
FROM
#tree
INNER JOIN
#ProjectRelations ON
#tree.emp=#Projectrelations.emp
INNER JOIN
#Projects ON
#Projectrelations.projectid=#Projects.projectid
WHERE
#tree.lval >= (
SELECT
lval
FROM
#tree
WHERE
#tree.emp='Bob'
)
Я не знаю, помогает ли это, но деревья — это боль в SQL, и мое личное мнение — это чувство различного зла, вложенные множества менее ужасны.
Другие решения
Других решений пока нет …