Иерархическая фильтрация с использованием SQL
У меня есть 3 примера таблиц в моей базе данных, как показано ниже: (1) Организационная структура (2) Пользователь (3) Счет-фактура
Я хотел бы использовать SQL для написания представления с функциями динамической фильтрации на основе иерархии. Например, если я пользователь «Джек», который является руководителем ИТ-отдела, при выполнении представления я должен видеть только идентификаторы счета-фактуры 1 и 3, поскольку идентификатор счета-фактуры 1 принадлежит мне, а идентификатор счета-фактуры 3 принадлежит мне. еще одна сотрудница «Николь» моего ИТ-отдела. Кроме того, я не смогу видеть идентификаторы счетов 2 и 4, поскольку эти счета не принадлежат пользователям ИТ-отдела.
Возможно ли этого добиться с помощью SQL в любой реляционной базе данных? Если да, пожалуйста, также предоставьте операторы SQL, которые могут этого достичь. Спасибо.
Да, этот раздел также может находиться в столбце «Команда». Если Команда сотрудника является секцией, это означает, что этот сотрудник является менеджером секции. Если Полла выполняет представление, она должна видеть только принадлежащий ей счет (и все счета в единице закупок, если таковые имеются).
Ответы 1
Надеюсь, это вам поможет:
WITH
a AS
(SELECT team FROM user
WHERE user_id=1),
b AS (
SELECT DISTINCT department as team, department as sub_team
FROM org
UNION
SELECT DISTINCT department as team, section as sub_team
FROM org
UNION
SELECT DISTINCT department as team, unit as sub_team
FROM org
UNION
SELECT DISTINCT section as team, section as sub_team
FROM org
UNION
SELECT DISTINCT section as team, unit as sub_team
FROM org
UNION
SELECT DISTINCT unit as team, unit as sub_team
FROM org),
c AS (
SELECT b.sub_team FROM a, b
WHERE a.team=b.team
),
d AS (
SELECT user_id FROM user, c
WHERE user.team=c.sub_team
)
SELECT invoice_id FROM invoice, d
WHERE invoice.user_id = d.user_id;
Возможно, есть более элегантное решение, но оно работает.
Спасибо за этот запрос, он работает! Еще один вопрос о другом случае: если я хочу, чтобы фильтрация была следующей: Например, я Полла, когда я запускаю запрос, даже если моя «Команда» в таблице «Пользователь» — «Закупки», я должен видеть только счет-фактура принадлежит мне (идентификаторы счета-фактуры 2 и 4), но не принадлежат никаким другим счетам от других пользователей в разделе «Покупки». Как нам следует изменить запрос, чтобы добиться этого?
@ryan9025 Каким должен быть результат, если мы выполним запрос для Джека? Каков результат для Николь?
В этом другом случае: для Джека результат должен снова вернуть счета 1 и 3. Для Николь результат должен снова вернуть 1. Я также могу проиллюстрировать это дополнительно, например, мы добавляем новый счет (5, 800, 4) как (invoice_id , сумма, user_id), а также нового пользователя (4, «Крис», «Покупки»), то когда Полла запускает запрос, он должен возвращать только идентификаторы своего счета 2 и 4 и не возвращать идентификатор счета 5 (поскольку счет-фактура с идентификатором 5 принадлежит Крису).
@ryan9025 Вы можете использовать оператор минус (geeksforgeeks.org/sql-minus-operator) из нашего результата. Вам нужно минусовать все счета пользователей с той же командой, но с user_id != нашего пользователя.
Спасибо за ответ. Дело в том, что, к сожалению, я не знаю, кто будет в этой команде в реальном случае использования, то есть некоторые пользователи будут выполнять этот запрос, но они не смогут изменять запрос на лету. Фактически, для user_id в нашем текущем запросе я также заменю его переменной, которая будет автоматически получать user_id пользователя, выполняющего запрос (наша система — Google Bigquery и имеет такую встроенную переменную/функцию). Таким образом, можно ли создать представление для этого другого случая, не зная, кто является пользователями в команде?
извините, может быть, я не очень хорошо объяснил в своем последнем комментарии... во втором упомянутом мной варианте использования я создам представление, которое будет автоматически получать user_id, когда пользователь запускает запрос. Таким образом, я не буду знать, кто пользователь, выполняющий запрос. Из-за этого мы не можем использовать «минус» для исключения других пользователей в той же команде, поскольку мы не знаем, какие пользователи будут выполнять запрос. Можно ли изменить предоставленный вами первый запрос, чтобы при выполнении запроса Полла видела только свои счета, а не счета других людей, даже если они находятся в одной команде?
Может ли это быть раздел в столбце «Команда»? Каким должно быть поведение, если Полла выполнит представление?