Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Where all in - где все строки есть в подзапросе  [new]
Alex_BBB
Member

Откуда:
Сообщений: 1197
Добрый день, помогите пожалуйста с запросом.

Есть:
-- роли
DECLARE @Roles TABLE
(
  Id int, 
  Title nvarchar(10)
)

-- шаблоны назначения ролей
-- в шаблоне может быть несколько ролей
DECLARE @RoleTemplates TABLE
(
  Id int, 
  Title nvarchar(10)
)

-- Template многие_ко_многим Role
DECLARE @RoleTemplateToRole TABLE
(
	TemplateId int,
	RoleId int
)


-- таблица назначения ролей
-- Principal многие_ко_многим Role 
DECLARE @RoleAssigments TABLE
(
   PrincipalId int,
   RoleId int
)


Нужно получить шаблоны для Principal-ов.
Если у Principal есть все роли шаблона, то считаем что у Principal есть шаблон.
В идеале вот такая View нужна
PrincipalId int
TemplateId int


insert into @Roles values (1, 'Role1')
insert into @Roles values (2, 'Role2')
insert into @Roles values (3, 'Role3')

insert into @RoleTemplates values (1, 'Template1')

insert into @RoleTemplateToRole values (1, 1)
insert into @RoleTemplateToRole values (1, 2)


insert into @RoleAssigments values (1, 1)
insert into @RoleAssigments values (1, 2)
insert into @RoleAssigments values (1, 3)

insert into @RoleAssigments values (2, 1)
insert into @RoleAssigments values (2, 3)


-- это не правильный запрос для примера
select 
	ass.PrincipalID,
	ass.RoleID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId
24 окт 19, 17:50    [22001948]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
court
Member

Откуда:
Сообщений: 1991
Alex_BBB
Если у Principal есть все роли шаблона, то считаем что у Principal есть шаблон.

реляционное деление (?)
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId
having count(distinct ass.RoleID) = (select count(*) from @RoleTemplateToRole xz where xz.TemplateId = templToRole.TemplateId)
24 окт 19, 18:04    [22001966]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
Alex_BBB
Member

Откуда:
Сообщений: 1197
court, спасибо! Похоже оно самое
24 окт 19, 18:30    [22002006]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
aleks222
Member

Откуда:
Сообщений: 929
court
Alex_BBB
Если у Principal есть все роли шаблона, то считаем что у Principal есть шаблон.

реляционное деление (?)
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId
having count(distinct ass.RoleID) = (select count(*) from @RoleTemplateToRole xz where xz.TemplateId = templToRole.TemplateId)


Ужос. Иди выучи, наконец, exists.

ЗЫ. "есть все роли шаблона" = нет ролей, которых нет
25 окт 19, 05:43    [22002170]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
court
Member

Откуда:
Сообщений: 1991
aleks222
court
пропущено...

реляционное деление (?)
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId
having count(distinct ass.RoleID) = (select count(*) from @RoleTemplateToRole xz where xz.TemplateId = templToRole.TemplateId)



Ужос. Иди выучи, наконец, exists.

ЗЫ. "есть все роли шаблона" = нет ролей, которых нет
ну так "изобрази"
Потом "померяемся" :)
25 окт 19, 09:45    [22002254]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
court
Member

Откуда:
Сообщений: 1991
Alex_BBB,

так ещё можно. Может лучше будет ...
;with cte as 
	(select *, count(*)over(partition by TemplateId) as cntAll from @RoleTemplateToRole)
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join cte templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId,
	templToRole.cntAll
having count(*) = templToRole.cntAll
25 окт 19, 09:46    [22002257]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Вот, мы как-то обсуждали: Как реализовать операцию реляционной алгебры "деление" на SQL?
25 окт 19, 09:55    [22002267]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
court
aleks222
пропущено...


Ужос. Иди выучи, наконец, exists.

ЗЫ. "есть все роли шаблона" = нет ролей, которых нет
ну так "изобрази"
Потом "померяемся" :)
Не хватает таблички

DECLARE @Principals table (PrincipalId int)
INSERT INTO @Principals (PrincipalId) VALUES (1), (2)


Запрос будет такой:

SELECT  
    * 
FROM    
    @Principals p
    INNER JOIN 
        @RoleTemplates t
            ON NOT EXISTS (
                SELECT 
                    * 
                FROM 
                    @RoleTemplateToRole tr 
                    LEFT JOIN 
                        @RoleAssigments ra 
                            ON ra.PrincipalId = p.PrincipalId 
                            AND ra.RoleId = tr.RoleId
                WHERE tr.TemplateId = t.Id
                    AND ra.RoleId IS NULL)


Померять было бы неплохо, потому что я не уверен, что это будет быстрее, но на этих данных все по 0, нужно либо на реальных данных смотреть, либо нагенерить побольше вариантов.
25 окт 19, 10:06    [22002281]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
court
Member

Откуда:
Сообщений: 1991
iap
Вот, мы как-то обсуждали: Как реализовать операцию реляционной алгебры "деление" на SQL?

вот тут ещё "было интересно" - запрос реляционное деление
25 окт 19, 12:19    [22002445]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
court
Member

Откуда:
Сообщений: 1991
Minamoto
court
пропущено...
ну так "изобрази"
Потом "померяемся" :)
Не хватает таблички

DECLARE @Principals table (PrincipalId int)
INSERT INTO @Principals (PrincipalId) VALUES (1), (2)



Запрос будет такой:

SELECT  
    * 
FROM    
    @Principals p
    INNER JOIN 
        @RoleTemplates t
            ON NOT EXISTS (
                SELECT 
                    * 
                FROM 
                    @RoleTemplateToRole tr 
                    LEFT JOIN 
                        @RoleAssigments ra 
                            ON ra.PrincipalId = p.PrincipalId 
                            AND ra.RoleId = tr.RoleId
                WHERE tr.TemplateId = t.Id
                    AND ra.RoleId IS NULL)



Померять было бы неплохо, потому что я не уверен, что это будет быстрее, но на этих данных все по 0, нужно либо на реальных данных смотреть, либо нагенерить побольше вариантов.
как минимум стоимость запроса, для вашего, у меня "пишет" в 3-и раза меньше чем моего ...

пс
но, кстате, эти запросы, совершенно по разному будут "реагировать" на "пустой" шаблон
+
-- роли
DECLARE @Roles TABLE
(
  Id int, 
  Title nvarchar(10)
)

-- шаблоны назначения ролей
-- в шаблоне может быть несколько ролей
DECLARE @RoleTemplates TABLE
(
  Id int, 
  Title nvarchar(10)
)

-- Template многие_ко_многим Role
DECLARE @RoleTemplateToRole TABLE
(
	TemplateId int,
	RoleId int
)


-- таблица назначения ролей
-- Principal многие_ко_многим Role 
DECLARE @RoleAssigments TABLE
(
   PrincipalId int,
   RoleId int
)
DECLARE @Principals table (PrincipalId int)
INSERT INTO @Principals (PrincipalId) VALUES (1), (2)


insert into @Roles values (1, 'Role1')
insert into @Roles values (2, 'Role2')
insert into @Roles values (3, 'Role3')

insert into @RoleTemplates values (1, 'Template1')

--insert into @RoleTemplateToRole values (1, 1)
--insert into @RoleTemplateToRole values (1, 2)
--insert into @RoleTemplateToRole values (1, 4)


insert into @RoleAssigments values (1, 1)
insert into @RoleAssigments values (1, 2)
insert into @RoleAssigments values (1, 3)
insert into @RoleAssigments values (1, 4)

insert into @RoleAssigments values (2, 1)
insert into @RoleAssigments values (2, 2)
insert into @RoleAssigments values (2, 3)

--	***	1	
select 
	ass.PrincipalID,
	templToRole.TemplateId
from @RoleAssigments ass
join @RoleTemplateToRole templToRole ON ass.RoleID = templToRole.RoleId
group by
	ass.PrincipalID,
	templToRole.TemplateId
having count(*) = (select count(*) from @RoleTemplateToRole xz where xz.TemplateId = templToRole.TemplateId)

--	***	2	
SELECT  
    * 
FROM    
    @Principals p
    INNER JOIN 
        @RoleTemplates t
            ON NOT EXISTS (
                SELECT 
                    * 
                FROM 
                    @RoleTemplateToRole tr 
                    LEFT JOIN 
                        @RoleAssigments ra 
                            ON ra.PrincipalId = p.PrincipalId 
                            AND ra.RoleId = tr.RoleId
                WHERE tr.TemplateId = t.Id
                    AND ra.RoleId IS NULL)
25 окт 19, 12:25    [22002460]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
court
Member

Откуда:
Сообщений: 1991
court
пс
но, кстате, эти запросы, совершенно по разному будут "реагировать" на "пустой" шаблон

-- *** 1
PrincipalIDTemplateId


-- *** 2
PrincipalIdIdTitle
11Template1
21Template1
25 окт 19, 12:26    [22002464]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
court
но, кстате, эти запросы, совершенно по разному будут "реагировать" на "пустой" шаблон

Да, и это скорее в пользу решения с группировкой, т.к., по идее, фраза "Если у Principal есть все роли шаблона, то считаем что у Principal есть шаблон.", подразумевает, что у шаблона есть роли. Но в мой запрос тоже несложно добавить это условие - через ещё один exists, например.
25 окт 19, 12:59    [22002488]     Ответить | Цитировать Сообщить модератору
 Re: Where all in - где все строки есть в подзапросе  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
court
iap
Вот, мы как-то обсуждали: Как реализовать операцию реляционной алгебры "деление" на SQL?

вот тут ещё "было интересно" - запрос реляционное деление
Чего мелочиться?




25 окт 19, 13:07    [22002496]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить