Блог

Хорошо, я надеюсь, что смогу достаточно хорошо объяснить этот вопрос, потому что я чувствую, что это будет непросто.

У меня есть две таблицы, с которыми я работаю сегодня. Они выглядят так:

 @pset table (PersonID int, SystemID int, EntitlementID int, TargetID int)

@Connector table (TargetName varchar(10), fConnector bit)
 

В первой таблице хранятся записи, которые сообщают мне, о, у этого человека есть эта система, которая состоит из этих прав, у которых есть эти цели. Немного сложно, но оставайтесь со мной. Во втором хранится имя цели, а затем, имеет ли эта цель соединитель в моей не очень теоретической системе.

Что я пытаюсь сделать, так это объединить эти две таблицы, чтобы я мог видеть целевой флаг для каждой строки в @pset. Это поможет мне позже, как вы увидите.

Если каждое право доступа в системе имеет соединитель с целевым объектом (флаг имеет значение true для всех из них), тогда я хотел бы знать.

Все остальные должны перейти в другую таблицу.

Это то, что я пытался сделать, но это не сработало. Мне нужно знать, где я ошибся. Надеюсь, кто-то с большим опытом, чем я, сможет ответить.

 -- If the count(123) = 10 (ten rows with SystemID = 123) and the sum = 10, cool.
select pset.*, conn.fConnector from @pset pset
inner join vuTargets vt
on vt.TargetID = pset.TargetID
inner join @conn conn
on  conn.TargetName = vt.TargetName
group by ProfileID, SystemRoleID, EntitlementID, TargetID, fConnector
having count(SystemID) = sum(cast(fConnector as int))
order by ProfileID
 

и

 -- If the count(123) = 10 (ten rows with SystemID = 123) and the sum <> 10
select pset.*, conn.fConnector from @pset pset
inner join vuTargets vt
on vt.TargetID = pset.TargetID
inner join @conn conn
on  conn.TargetName = vt.TargetName
group by ProfileID, SystemRoleID, EntitlementID, TargetID, fConnector
having count(SystemID) <> sum(cast(fConnector as int))
order by ProfileID
 

К сожалению, они не работают: (

Редактировать

Вот скриншот, показывающий проблему. Обратите внимание, что ProfileID 1599 имеет SystemID 1126567, но у одного из прав доступа нет соединителя! Как я могу вставить обе эти строки во второй запрос? (выше)

Ваша основная проблема заключается в том, что вы пытаетесь свернуть до двух разных наборов записей.
Начальный набор ( SELECT GROUP BY предложения and ) говорит, что вам нужна одна запись для каждого различия в наборе [ProfileID, SystemID, EntitlementId, TargetID, fConnector].
Во втором наборе ( HAVING предложении) говорится, что вы хотите для каждой строки в начальном наборе сравнивать COUNT количество записей с SUM количеством соединений. Однако, поскольку вы запросили группировку до отдельного флага, это приводит к получению одной строки для каждого флага (при условии отношения 1 к 1). Фактически, вы говорите: «Эй, если у этой цели есть соединение? Да, я этого хочу ‘.

Похоже, вам нужно свернуть SystemId значение. Для этого вам нужно будет изменить ваши SELECT GROUP BY предложения and, чтобы включить только set [ProfileID, SystemID] . Это вернет только те строки (с ключом из профиля и системы), у которых все цели «подключены». Вы не сможете увидеть отдельные права доступа, целевые объекты и связаны ли они (однако вы сможете сделать вывод, что все они будут / не будут подключены).

Редактировать:

В интересах полного раскрытия, вот как вы получите что-то похожее на ваш исходный набор результатов, где перечислены все EntitlementId s и TargetId s:

 WITH all_connections as (SELECT pset.ProfileId, pset.SystemRoleId
                         FROM @pset pset
                         INNER JOIN vuTargets vt
                         ON vt.TargetId = pset.TargetId
                         INNER JOIN @conn conn
                         ON conn.TargetName = vt.TargetName
                         GROUP BY pset.ProfileId, pset.SystemRoleId
                         HAVING COUNT(pset.SystemRoleId) 
                                                  = SUM(CAST(fConnector as INT)))

SELECT pset.*
FROM @pset pset
JOIN all_connections conn
ON conn.ProfileId = pset.ProfileId
AND conn.SystemRoleId = pset.SystemRoleId
 

Это должно дать вам список, вплоть до TargetId ProfileId / SystemRoleId keys, где все EntitlementId s и TargetId s имеют соединение (или переключите CTE = на <> для тех, где не все).

Редактировать: исправлены мои исходные запросы, а также обновлено описание

Вы можете разделить это: сначала найдите TargetID s, которые имеют fConnector значение 0. Затем найдите PersonID SystemID пары, которые имеют любую цель, равную той, которую вы нашли. Затем выберите соответствующие данные: (это позволяет найти PersonID SystemID пару, в которой по крайней мере одно право не имеет соединителя с целевым объектом)

 with abc as (
    select PersonID, SystemID
    from pset P
    where TargetID in (
        select TargetID
        from vuTargets V join connector C on V.TargetName = C.TargetName
        where C.fConnector = 0
        )
)
select P.PersonID, P.SystemID, P.EntitlementID, P.TargetID, C.fConnector
from pset P
    join abc on ((P.PersonID = abc.PersonID) and (P.SystemID = abc.SystemID))
    join vuTargets V on P.TargetID = V.TargetID
    join connector C on V.TargetName = C.TargetName
 

Запрос на поиск PersonID SystemID пар, в которых все права имеют соединитель с целью, аналогичен:

 with abc as (
    select PersonID, SystemID
    from pset P
    where TargetID in (
        select TargetID
        from vuTargets V join connector C on V.TargetName = C.TargetName
        where C.fConnector = 0
        )
)
select P.PersonID, P.SystemID, P.EntitlementID, P.TargetID, C.fConnector
from 
    pset P
    join abc on ((P.PersonID <> abc.PersonID) or (P.SystemID <> abc.SystemID))
    join vuTargets V on P.TargetID = V.TargetID
    join connector C on V.TargetName = C.TargetName
 

Разница заключается в соединении с временной таблицей ( <> vs = ). Это очень похоже на ответ зеро, но не использует подсчеты или суммы.