Самостоятельно присоединиться для сравнения строк таблицы?
Мы используем SQL Server 2017 для нашего приложения для управления документами. В нашем приложении один документ может иметь множество версий. Доступ может быть предоставлен пользователям на уровне версии. Мы храним информацию о доступе пользователей в таблице DOCUMENT_ACL.
- UserId 1001, 1002 и 1003 имеет доступ к RevisionId 101.
- UserId 1003 и 1004 имеет доступ к RevisionId 102.
- UserId 1004 и 1005 имеет доступ к RevisionId 103.
У нас есть сложное требование показывать только последнюю версию, к которой у пользователя есть доступ.
Мы хотим написать хранимую процедуру для возврата набора результатов, как показано ниже.
например
Для RevisionId 103 (Версия 3 — последняя) он должен возвращать UserId 1004 и 1005.
Для RevisionId 102 (Версия 2) он должен возвращать только UserId 1003 (поскольку пользователь 1004 уже возвращается для версии 103).
Для RevisionId 101 (версия 1) он должен возвращать только UserId 1001 и 1002 (поскольку пользователь 1003 уже возвращается для версии 102).
Мы рассмотрели способ самостоятельного объединения, но он выглядит слишком сложным. Пожалуйста, предложите, как мы можем реализовать вышеуказанную логику в хранимой процедуре.
В соответствии с руководством по вопросам не публикуйте изображения кода, данных, сообщений об ошибках и т. д. — скопируйте или введите текст в вопрос. Пожалуйста, ограничьте использование изображений для диаграмм или демонстрации ошибок рендеринга, вещей, которые невозможно точно описать с помощью текста.
«но это выглядит слишком сложно» Почему это выглядит сложным? Что именно вы пробовали?
С точки зрения бизнеса нет особого смысла иметь более одной активной/эффективной версии документа в системе управления документами. Обычно последняя версия является «активной», а все предыдущие версии — «устаревшими». Каковы требования для доступа к пользовательской версии?
Ответы 3
Звучит как простая группа?
with data as (
select docid, rev
, users
from (
values (11, 101, 1001)
, (11, 101, 1002)
, (11, 101, 1003)
, (11, 102, 1003)
, (11, 102, 1004)
, (11, 103, 1004)
, (11, 103, 1005)
) x(docid,rev, users)
)
select docid, maxrev, string_agg(users,',') as users
from (
select docid, max(rev) as maxrev, users
from data
group by docid, users
) x
group by docid, maxrev
Выходы:
Чтобы получить ожидаемый результат, мы можем написать SQL-запрос, используя функцию ROW_NUMBER(), чтобы определить последний идентификатор версии, сгруппированный по идентификатору пользователя.
Вот запрос:
DECLARE @vTbl AS TABLE
(
DocId INT NULL
, RevisionId INT NULL
, UserID INT NULL
)
INSERT INTO @vTbl (DocId, RevisionId, UserID)
SELECT 11 AS DocID, 101 AS RevisionId, 1001 AS UserID
UNION ALL SELECT 11, 101, 1002
UNION ALL SELECT 11, 101, 1003
UNION ALL SELECT 11, 102, 1003
UNION ALL SELECT 11, 102, 1004
UNION ALL SELECT 11, 103, 1004
UNION ALL SELECT 11, 103, 1005;
SELECT a.DocId, a.RevisionId, a.UserID
FROM @vTbl AS a
SELECT DISTINCT
a.DocId
, a.RevisionId
, (
SELECT STRING_AGG(t.UserId, ',')
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DocId, RevisionId DESC) AS RNo, * FROM @vTbl
) AS t
WHERE t.RNo = 1
AND t.DocId = a.DocId
AND t.RevisionId= a.RevisionId
) AS UserIdCsv2
FROM @vTbl AS a;
GO
Результат:
Выберите все версии и исключите пользователей, у которых доступна более поздняя версия:
SELECT
da1.RevisionId,
STRING_AGG(da1.UserId,',') UserIds
FROM DOCUMENT_ACL da1
WHERE da1.UserId NOT IN (SELECT da2.UserId
FROM DOCUMENT_ACL da2
WHERE da2.RevisionId > da1.RevisionId)
GROUP BY da1.RevisionId
см.: DBFIDDLE
это предполагает, что данные такие:
CREATE TABLE DOCUMENT_ACL (
DocID INT,
RevisionId INT,
UserId INT);
INSERT INTO DOCUMENT_ACL VALUES
(11,101,1001),
(11,101,1002),
(11,101,1003),
(11,102,1003),
(11,102,1004),
(11,103,1004),
(11,103,1005);
Это ответ на ваш вопрос? Получите 1 верхнюю строку каждой группы и Результаты в SQL, разделенные запятыми