SQL Server多个联接返回重复的值

Question

我的数据库TEST:下有4个表

1. 用户
2. 用户组权限
3. PERMISSION_TYPE
4. 组

当我使用内部联接和交叉联接而不是返回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

Answer 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);

(如果你比IN更喜欢的话，你也可以加入permission_type到user_group_permission。(

Answer 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

因此，最重要的索引是users(name, active, upper(DISPLAY_VERSION))。