如何检索与第一个子查询匹配所有行的记录

发布于 2024-12-11 12:16:58 字数 832 浏览 0 评论 0原文

您好，需要帮助来解决一些复杂的查询。我会将输入用户名（例如 test ）发送到查询。我必须列出与用户名 test 具有相同角色的其他用户，并且这些用户名应该与用户名 test 的所有组 ID 相匹配。如果其他用户拥有的组数多于用户名 test 则不是问题，但应匹配用户名 test 中存在的所有 groupdid。我尝试了以下查询。如果所有 groupid 都匹配，它将不会返回任何行。但是，我需要与所有组 ID 匹配的用户名，而不是不返回完全匹配的行。

select 
  group_id 
from 
  user_info us ,group_privilege_details gp 
where 
  login_name='test' 
  and us.user_id = gp.user_id 
EXCEPT
select 
  group_id 
from 
  user_info u ,group_privilege_details g 
where login_name !='test' 
and role_id in (select role_id 
                from user_info 
                where login_name ='test')
and group_id in (select group_id 
                 from user_info us ,group_privilege_details gp 
                 where login_name='test' 
                 and us.user_id = gp.user_id ) 
and g.user_id = u.user_id

提前致谢。抱歉很长的解释

原文

Hi Need help to resolve some complicated query. I'll send the input username (say test ) to the query. I have to list the other users with the same role of the username test and also those usernames should be match with all the groupid of the username test. If the other user has more groups than username test is not an issue, but should match all the groupdid exists in the username test. I tried the following query. It wil return no rows if all the groupids matches. But, I need the username those who have match with all the group id instead of returning no rows for the exact match.

select 
  group_id 
from 
  user_info us ,group_privilege_details gp 
where 
  login_name='test' 
  and us.user_id = gp.user_id 
EXCEPT
select 
  group_id 
from 
  user_info u ,group_privilege_details g 
where login_name !='test' 
and role_id in (select role_id 
                from user_info 
                where login_name ='test')
and group_id in (select group_id 
                 from user_info us ,group_privilege_details gp 
                 where login_name='test' 
                 and us.user_id = gp.user_id ) 
and g.user_id = u.user_id

Thanks in advance. And sorry for long explanation

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静水深流 2024-12-18 12:16:58

如果我理解正确的话，你想要：

select all users,
        not being user test,
        with same role as user test,
        where not exists a group where user test is a member of
            and the other user is not a member of

在 SQL 中，类似于以下内容。我使用了以下缩写：OU 其他用户、TU 测试用户、TUG 测试用户组、OUG 其他用户组。

select OU.login_name, OU.user_id
    from user_info OU, user_info TU
    where OU.user_id <> TU.user_id 
        and OU.role_id = TU.role_id 
        and TU.login_name = 'test'
        and not exists (
            select * from group_privilege_details TUG
                where TUG.user_id = TU.user_id
                    and TUG.group_id not in (
                        select group_id 
                            from group_privilege_details OUG 
                            where OUG.user_id = OU.id
                    )
        )

If I understand correctly you want:

select all users,
        not being user test,
        with same role as user test,
        where not exists a group where user test is a member of
            and the other user is not a member of

In SQL that is something like the following. I used the following abbreviations: OU other user, TU test user, TUG test user's groups, OUG other user's groups.

select OU.login_name, OU.user_id
    from user_info OU, user_info TU
    where OU.user_id <> TU.user_id 
        and OU.role_id = TU.role_id 
        and TU.login_name = 'test'
        and not exists (
            select * from group_privilege_details TUG
                where TUG.user_id = TU.user_id
                    and TUG.group_id not in (
                        select group_id 
                            from group_privilege_details OUG 
                            where OUG.user_id = OU.id
                    )
        )

回复收藏 0 原文

~没有更多了~