Проблема с SQL-запросом в PostgreSQL
У меня есть три таблицы для пользователей и прав доступа пользователей, как показано ниже:
create table users (id int, login varchar, organization varchar);
create table groups (id int, name varchar);
create table user_groups (gid int, uid int,
constraint fk_users_groups foreign key(gid) references groups(id),
constraint fk_users_users foreign key(uid) references users(id)
);
Давайте вставим несколько записей:
insert into groups values(1, 'Data Entry'), (2, 'Sender'), (3, 'Receiver'),
(4, 'Reviewer'), (5, 'Checker'), (6, 'Approver'), (7, 'Nothing'), (8,
'Everything'), (9, 'HR Systems'), (10, 'Check System'), (11, 'Final');
insert into users (1, 'Ahmad'), (2, 'Sam'), (3, 'John'), (4, 'Smith'),
(5, 'Roy');
insert into user_groups values (1, 1), (2, 1), (3, 1), (8, 1), (9, 1), (4, 2),
(5, 2), (10, 2), (11, 2), (6, 3), (7, 4);
Теперь я хочу получить идентификатор пользователя, логин и тип пользователя как таковые, если у пользователя есть группы («ввод данных», «отправитель», «получатель»), тогда должен быть напечатан «HR» (нас не интересуют другие группы есть ли у него или нет, например: здесь у него есть и другие группы, а не нам, которые нужны для типа пользователя "HR") для него в качестве типа пользователя, если он / она находится в группах ("проверяющий", "проверяющий") затем следует напечатать «Check», иначе «Any» следует напечатать для любых других групп.
Желаемый результат:
User ID Login UserType
1 Ahmad HR
2 Sam Check
3 John Any
4 Smith Any
Ответы 4
Вы можете использовать max(case(, чтобы найти верхнюю группу, в которой кто-то находится. Здесь я определил порядок, добавив к типу пользователя префикс 1, 2 или 3:
select u.id
, login
, substring(max(case
when name in ('Data Entry', 'Sender', 'Receiver') THEN '3 HR'
when name in ('Reviewer', 'Checker') THEN '2 Check'
else '3 Any'
end) from 3)
from users u
left join
user_groups ug
on ug.uid = u.id
left join
groups g
on g.id = ug.gid
group by
u.id
, login
Вы можете использовать:
SELECT DISTINCT u.id AS user_id, u.login,
CASE WHEN g.name IN ('Data Entry', 'Sender', 'Receiver') THEN 'HR'
WHEN g.name IN ('Reviewer', 'Checker') THEN 'Check'
ELSE 'Any'
END AS userType
FROM users u
JOIN user_groups ug ON u.id = ug.uid
JOIN groups g ON ug.gid= g.id
ORDER BY u.id;
Добавьте столбец в группы таблиц, содержащий то, что следует распечатать, и когда вам в следующий раз потребуется добавить новое значение в группы, вы будете рады, что это сделали!
изменить группы таблиц добавить текст пользовательского типа столбца; группы обновлений устанавливают usertype = 'HR', где id от 1 до 3; группы обновлений устанавливают usertype = 'Check', где id от 4 до 5;
SELECT DISTINCT u.id AS user_id, u.login,
COALESCE(g.usertype,'any')
FROM users u
JOIN user_groups ug ON u.id = ug.uid
LEFT OUTER JOIN groups g ON ug.gid= g.id and g.userype is not null
ORDER BY u.id;
это напечатает две строки для любого пользователя, у которого есть и HR, и Check, и одну строку для всех остальных.
В Postgres DISTINCT ON может быть самым простым подходом:
SELECT DISTINCT ON (u.id) u.id AS user_id, u.login, g.userType
FROM users u JOIN
user_groups ug
ON u.id = ug.uid JOIN
(SELECT g.*,
(CASE WHEN g.name IN ('Data Entry', 'Sender', 'Receiver') THEN 'HR'
WHEN g.name IN ('Reviewer', 'Checker') THEN 'Check'
ELSE 'Any'
END) AS userType,
(CASE WHEN g.name IN ('Data Entry', 'Sender', 'Receiver') THEN 1
WHEN g.name IN ('Reviewer', 'Checker') THEN 2
ELSE 3
END) AS userType_priority
FROM groups g
) g
ON ug.gid = g.id
ORDER BY u.id, g.userType_priority;
Другая альтернатива - CASE EXISTS:
select u.*,
(case when exists (select 1
from user_groups u join
groups g
on ug.gid = g.id
where g.name in ('Data Entry', 'Sender', 'Receiver')
)
then 'HR'
when exists (select 1
from user_groups u join
groups g
on ug.gid = g.id
where g.name in ('Reviewer', 'Checker')
)
then 'Check'
else 'Any'
end) as UserType
from users u;
Несмотря на сложный вид запроса, он не включает агрегирования или сортировки, поэтому он может иметь лучшую производительность.
Я бы рекомендовал включить UserType в таблицу groups. Это значительно упростит запрос и сделает любые приложения, использующие UserType, более согласованными.
@Andomar Это зависит от обстоятельств. С несколькими группами вы получите более одной строки. Тогда как в вашем решении я получу только один (МАКС).