Блог

У меня есть три таблицы:

 User -- contains users
Group -- contains a set of groups that users can be members of
Membership -- Contains PKs from User and Group to indicate that User is a member of group
  

PK — это userId, groupId и MembershipId соответственно, а членство имеет FK для двух других таблиц.

Это довольно стандартный материал, единственная настройка заключается в том, что пользователь может быть членом нескольких групп, а группы имеют уровень (1, 2, 3, 4). Текущая группа пользователя — это группа, членом которой он является, с самым высоким уровнем.

Теперь я хочу получить подсчет количества участников в каждой группе. На данный момент у меня есть что-то вроде этого:

 SELECT Count(*) FROM Membership
    FROM Membership M1
    INNER JOIN Group G1 ON M1.GroupId = G1.GroupId
    INNER JOIN User ON User.UserId = M1.UserId
WHERE
    M1.GroupId = @groupId
    AND NOT EXISTS (
            SELECT * FROM Membership M2
            INNER JOIN Group G2 ON M2.GroupId = G2.GroupId
            WHERE M2.UserId = M1.UserId
            AND G1.Priority < G2.Priority)
  

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

В идеале я хотел бы преобразовать его так, чтобы я получал подсчеты для всех групп, а не только для одного выбора (как указано выше).)

Есть идеи, как сделать это более эффективно?

Какая СУБД? это будет работать в SQL Server…

 SELECT
    HighestGroupId,
    COUNT(*) AS Members
FROM
(
    SELECT
        m.UserID,
        MAX(m.GroupId) AS HighestGroupId
    FROM Membership m
    GROUP BY m.UserID
) sub
GROUP BY HighestGroupId