#sql #sql-server #join #duplicates
#sql #sql-server #Присоединиться #дубликаты
Вопрос:
У меня есть 4 таблицы в рамках моего теста базы данных:
- ПОЛЬЗОВАТЕЛЬ
- USER_GROUP_PERMISSION
- PERMISSION_TYPE
- Группы
когда я соединяю приведенные выше таблицы с помощью внутреннего соединения и перекрестного соединения, вместо возврата 520 строк возвращается 2600 строк, большинство значений дублируются
Мне нужен конечный результат без дубликатов
Запрос:
select user.*
from TEST.USER user
inner join TEST.USER_GROUP_PERMISSION user_grp1
on user.APPLICATION_ID = user_grp1.APPLICATION_ID
inner join TEST.PERMISSION_TYPE permission2_
on user_grp1.PERMISSION_TYPE_ID = permission2_.PERMISSION_TYPE_ID
inner join TEST.GROUPS groups
on user_grp1.GROUP_ID = groups.GROUP_ID
cross join TEST.PERMISSION_TYPE permission4_
where user_grp1.PERMISSION_TYPE_ID = permission4_.PERMISSION_TYPE_ID
and groups.GROUP_ID in (101)
and permission4_.PERMISSION_TYPE in (0 , 1 , 2 , 3 , 4 , 5 , 6)
and user.NAME = 'ROBIN'
and user.ACTIVE = '1'
order by upper(user.DISPLAY_VERSION) asc
Комментарии:
1. Что вы ожидаете, если вы
CROSS JOIN
? ACROSS JOIN
«присоединяет» каждую строку в существующем наборе данных к каждой строке в объединенной таблице. Если вы получаете «дубликаты», то вашиON
предложения илиJOIN
типы неверны. Но без образцов данных, ожидаемых результатов или логики, которую вы пытаетесь достичь, мы не можем сообщить вам, в чем заключается исправление.
Ответ №1:
Если вы хотите показывать пользователям без дубликатов, выберите «только для пользователей». Не присоединяйтесь! Вот как должен выглядеть запрос:
select * from users where ...
Ваш запрос трудно понять, особенно с типом разрешения, присоединенным дважды, один раз даже с внутренним соединением, замаскированным под перекрестное соединение. Похоже, вам нужен пользователь ROBIN при условии, что он активен и его приложение имеет групповое разрешение для группы 101 и типа 1-6. Это было бы:
select *
from test.user
where name = 'ROBIN'
and active = '1'
and application_id in
(
select application_id
from test.user_group_permission
where group_id = 101
and permission_type_id in
(
select permission_type_id
from test.permission_type
where permission_type in (0, 1, 2, 3, 4, 5, 6)
)
)
order by upper(display_version);
(Вы также можете присоединиться permission_type
к user_group_permission
, если вам это нравится больше, чем IN
.)
Комментарии:
1. Это очень помогает, но все равно выполнение запроса занимает более 2,5 минут. это основная проблема с производительностью. Пожалуйста, предложите несколько идей для решения проблемы производительности
2. 2,5 минуты для одного пользователя??? Это странно. Скорость запроса почти всегда зависит от индексов. Поскольку у SQL Server, похоже, есть проблемы с запросом, я бы предоставил один индекс для каждой таблицы и перестановку ее столбцов, о которых идет речь: два индекса
permission_type
и столбцыpermission_type
иpermission_type_id
, шесть индексов дляuser_group_permission
со всеми перестановкамиgroup_id
,permission_type_id
,application_id
, шесть индексовuser
сname
,active
,application_id
.3. Пример для первых двух:
create index idx1 on permission_type (permission_type, permission_type_id);
,create index idx2 on permission_type (permission_type_id, permission_type);
. Когда закончите, проверьте, какие индексы используются, и удалите остальные.
Ответ №2:
Я бы сформулировал запрос с помощью exists
:
select user.*
from TEST.USER user
where user.NAME = 'ROBIN'
user.ACTIVE = '1' and
exists (select 1
from TEST.USER_GROUP_PERMISSION user_grp1 join
TEST.PERMISSION_TYPE permission2_
on user_grp1.PERMISSION_TYPE_ID = permission2_.PERMISSION_TYPE_ID join
TEST.GROUPS groups
on user_grp1.GROUP_ID = groups.GROUP_ID join
TEST.PERMISSION_TYPE permission4_
on user_grp1.PERMISSION_TYPE_ID = permission4_.PERMISSION_TYPE_ID
where user.APPLICATION_ID = user_grp1.APPLICATION_ID
groups.GROUP_ID in (101) and
permission4_.PERMISSION_TYPE in (0 , 1 , 2 , 3 , 4 , 5 , 6)
)
order by upper(user.DISPLAY_VERSION) asc
Тогда самый важный индекс для этого — on users(name, active, upper(DISPLAY_VERSION))
.